Reading data from csv file

[TomBarrand]

I am using a vbscript to pull some data out of a csv file and enter it into an sql server database. If the csv file contains data that came from a memo field in a database and the user pressed return the csv file interprets the return. This cases a problem as the data after the first return does not get entered into the database.

Any ideas on how I can get round this problem?

Thanks

 

[dilettante]

How did you create the CSV file?

Normally when you export data to CSV you have several options such as:

field delimiter
quote text or not quote text
record delimiter

You need to specify a non-default value for record delimiter. This needs to be something that was not allowed in your memo field. This is tough, because you usually cannot choose anything but printable characters. Often TAB is available though - but your memos may have allowed TAB chars too? You need to pick something, perhaps the | (pipe) character?

I'd use the Tabular Data Control to load the data from the CSV file into a recordset. The TDC processes CSV data directly, and you can easily set the delimiters to the proper values before opening the recordset.

If somebody handed you the CSV file so you can't re-export it properly... well you need to examine the raw text of the file to see if they used a record delimiter aside from CrLf or NewLine. If they did, use TDC with this record delimiter, or another technique besides the ReadLine method (if that was what you were doing).

You may have to use the Read( ) method and chunk things in a couple thousand bytes at a time. Then "crack off" records by scanning for the record delimiter using InStr( ), and doing more Read( )s and appending data as you work through the file.

If the file is tiny enough you can just ReadAll( ) the stuff into a single string of course.

I find that TDC works great though. Much of this depends on how you are populating the SQL Server database.

You could probably load up the recordset via TDC and flush it all to a matching empty table in SQL Server - no loop required.

You could also walk through the recordset one record at a time and brute-force it, but why work so hard?

Good luck, hope this helps!