1

I am trying to read a CSV file with VBScript and it is causing huge problems because it isn't recognizing a line break. The way I have right now is:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(server.mappath("my_csv_file.csv"), ForReading)

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    arrFields = Split(strLine, ",")

   LOOP_STUFF_HERE

Loop

The CSV file has several lines but the problem is that it is reading the CSV file all as one long line and the last item of each line is being combined with the first item of the next line because there is no comma after the last line (it's being created by a client of mine in Excel and then sent to me). My solution is that I have to open it up in a text editor, manually add a comma to the end of the lines and then remove the line breaks. This won't work in the long run because we are setting up an automated system.

Basically, I need to be able to to Split the lines on a line break (I've tried Split(strLine, "\n" but that doesn't seem to work) and then once they are split by line break, then split them by comma. It'd be a multidimensional array in other words.

I can't find out how to get VBScript to recognize the line breaks though. Any ideas? Thanks for your help.

MillerMedia
  • 3,651
  • 17
  • 71
  • 150
  • Have you tried `Split(strLine, CHAR(10))` ? Char(10) represents line feed. – Mad Dog Tannen Apr 24 '14 at 07:36
  • There would seem to be something wrong with the file. If 32 bit windows open the file in edit.exe. edit /70 /h my_csv_file.csv. In the status bar is the character code and the music symbol and diamond appear for CR and LF. – tony bd Apr 24 '14 at 08:00
  • Rather than using scripting.filesystemobject, you could try using the csv file as a database - see this question http://stackoverflow.com/questions/6981234/classic-asp-reading-csv-file-having-alphanumeric-data – John Apr 24 '14 at 12:03
  • 2
    @KayNelson Small correction. `CHAR()` is an Excel function. The VBScript equivalent is `CHR()`. But I agree with your suggestion, though `CHR()` isn't needed. `vbCrLf` is the default line-ender. If that doesn't work, try `vbLf` (popular in Unix) or `vbCr` (popular on Macs). Personally, I would just open the file in a hex editor and see what's there! – Bond Apr 24 '14 at 12:53
  • Thanks @Bond, so are you saying to use 'vbCrLf' instead of of the comma as the split character? – MillerMedia Apr 24 '14 at 14:27
  • 1
    As @Bond say's if `ReadLine()` is displaying the whole CSV in one line then your CSV is not using `vbCrLf` as the EOL marker. This gives you two options try `vbCr` or `vbLf` instead of `Split(strLine, "\n"` etc. Also agree if you open the file in a HEX editor you might get a better understanding of what is required. You would be looking for either `0D0A` (`vbCrLf`), `0D` (`vbCr`) or `0A` (`vbLf`). – user692942 Apr 24 '14 at 14:33
  • 1
    No, sorry. I'm saying your code above *should* work fine as long as each line in the CSV ends in a `vbCrLf`. Since your script isn't working properly, I'm guessing each line *doesn't* end in `vbCrLf`. So `ReadLine` isn't going to work for you. You may have to read the whole file (use the `ReadAll` function) and then split by `vbCr` or `vbLf` to get each line. Grab a free hex editor like `HxD` and open your CSV to see what line-endings are being used. – Bond Apr 24 '14 at 14:33
  • @Bond Depending on the size of the file `ReadAll()` might not be a good fit, but not sure what alternatives to suggest. You could use `Read()` to step through a character at a time and determine whether character read is a `vbCr` or `vbLf` then do something at that point while still streaming the file? If this is a one off though I'd be more inclined to just modify the file so it contains `vbCrLf` that way it will work with `ReadLine()`. – user692942 Apr 24 '14 at 14:45
  • @Lankymart That's true about `ReadAll()`. Maybe make a small CSV just for testing? I use Notepad2 and there's a nifty "Show Line Endings" feature. I'm sure other editors have something similar. As you suggest, stepping through the string would work, too. – Bond Apr 24 '14 at 15:01

1 Answers1

2

Reading a CSV with FileSystem Object?. don't reinvent the wheel, there are different and proven ways to do exactly what you want in ASP.

The most easy way it's using OLEDB Jet or OLEDB ACE driver to read the file

Basiclyyou need to create a OLEDB.Connection Object with a specific Connection String to the get all the data in the CSV as rows; later you can pass all the data as array using GetRows method or you can use the Recordset object directly.

Post related of using this functionality

ASP.NET (Ace) When reading a CSV file using a DataReader and the OLEDB Jet data provider, how can I control column data types?

ASP.NET (Ace) Microsoft.ACE.OLEDB.12.0 CSV ConnectionString

ASP-Classic (Jet) Reading csv file in classic asp. Problem: column values are truncated up to 300 characters

the Connection is almost the same for Jet or Ace driver (it's the important part).

Community
  • 1
  • 1
Rafael
  • 3,081
  • 6
  • 32
  • 53
  • I'm looking into this and it looks like you've set me on the right path but I can't figure out the syntax. The last one (Jet) seems to have the right syntax for the code I'm using but I'm super unfamiliar with exactly what it is doing. Am I supposed to just be updating the info from within the "server.MapPath" parentheses to the location of my CSV file? – MillerMedia Apr 24 '14 at 18:47
  • I've tried just using the first lines in that example `set connection = Server.CreateObject ("ADODB.Connection") connection.Open Connection_string` but that throws an error. I guess I'm not understanding how to set the connection up correctly? – MillerMedia Apr 24 '14 at 18:49
  • what looks like currently your connection string... (update yuor question accordling to your new code – Rafael Apr 27 '14 at 13:23