LF vs. CRLF of a CSV 1

[MoonMullen]

I have a CSV text file, created by javascript query, that Access does not "see" the LF however Excel can "see" the new line fine. Through VBA code I'm having Access read in the file and then write out with CRLF to each line. I then have to bring it back in for parsing and creating a table. The whole process is too slow, about 18 to 20 minutes, for 3,300 lines. Would it be better to bring in the file and hold it in memory as an array? If so, can the array be used to parse and create a table? Would this process be any faster? I am new to Access VBA, Excel is my preferred coding of VBA, and I don not fully understanding how arrays work or handling them. I

 

[richardii]

If you want to do this fast consider using gawk or wsed to replace the offending end of line char with something more useful. You could run gawk.exe from Access, with your csv file as the input file, and then import the file that gawk outputs. Sorry I can't be more specific.

 

[MichaelRed]

I'm just curious. How are you reading the fiel in Ms. Access VBA? IN the general sense, VBA will interpert LF, CR and LFCR as line feeds. I use "Line Input" to read CSV files with all combinations of the 'new line' without problems.

Further, 20 minutes for 3300 records is only 115 'lines' per minute (less than 2 /second). this does not seem like the processing rate for any computer in real use for business today. It almost sounds like byte operations, but slow even for that.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over