csv files

[JRL]

FMT is Delimited... any other choices?

Look at http://connectionstrings.com/textfile They provide 2 FMT= settings, Fixed and Delimited. They say you can set your delimiter with a registry setting.

[zabros2020]

BRILLIANT!!!

Thank you so much guys. Honestly, your work has been invaluable, I had zero knowledge of databases before, and now its starting to make sense
THANK YOU

I have a few quick questions... if thats OK

Looking at the database as a matrix with rows and columns, the code below allows me to access specific elements of the matrix by providing the indices of it's location

Code: Select all

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties='text;HDR=YES;FMT=Delimited'
DBConnect>ConStr,dbH
DBQuery>dbH,select * from myfile.csv,fields,num_recs,num_fields
Let>rec=0
Repeat>rec
  Let>rec=rec+1
  Let>field=0
  Repeat>field
    Let>field=field+1
    Let>this_field=fields_%rec%_%field%
    MessageModal>this_field
  Until>field=num_fields
Until>rec=num_recs

The code below, lets me write entire rows into my matrix, one row at a time.

Code: Select all

Let>path=C:\
Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%path%;Extended Properties='text;HDR=YES;FMT=Delimited'
DBConnect>ConStr,dbH
Let>sql=insert into myfile.csv (field1, field2) values ("milad", 2006)
DBExec>dbH,sql,res

Is there some way of making the best of both worlds, and writing values into my matrix (database) one element at a time as they come up? The people that areusing my scripts are required to press shift+esc a lot, and whenever this happens i can't reach the end of the script

Code: Select all

Let>sql=insert into myfile.csv (field1, field2) values ("hello", 2006)
DBExec>dbH,sql,res

and therefor are unable to populate any values. It would be great if i could populate them one at a time that way if manual abort is used, i can still have some values populated into my csv file.

Finally, why does the hello have to have "" around it? When i take these out, it doesn't work. is there a way to be able to populate cells without the quotes.
A typical line in my csv file would be as follows:

P123456,HQJ12345678,9:54:09 AM,ZABR-7OSH,1,123456789,987654321,0,Great,Not Bad,NA,NA,NA,234.21875

So as you can see its a combination of times, letters, numbers, letters and numbers.

Thanks again for all your help!!!