can't read csv file 2

[Rinze]

Hi all,

I've used several tips to read a csv-file. However I can't seem to get it to work.
I use the next code:

====================================
Dim fs, objTextfile
Set fs = CreateObject("Scripting.FileSystemObject")
dim arrStr

set objTextfile = fs.OpenTextFile("\\ict01\RevalidantmapAansturing\Databases\test.csv",1)

Do while NOT objTextFile.AtEndOfStream
arrStr = split(objTextFile.ReadLine,";")
strWaarde1 = arrStr(0)
strWaarde2 = arrStr(1)

Wscript.echo strWaarde1 & " " & strWaarde2

Loop

objTextFile.Close
set objTextFile = Nothing
set fs = nothing
============================================

The first echo gives a strange string of three characters. All next echo's are empty.
At last I get an error that the subscript is out of range for the array(1).

So, what am I doing wrong here? Can the csv (produced bij Cognos8 report) be corrupted? Whenever I use a text-file (txt) the script works fine.

Any help is appriciated.

Rinze Smit
Revalidatie Friesland

 

[taupirho]

It looks like you're using the wrong delimiter in your split function ; instead of ,


In order to understand recursion, you must first understand recursion.

 

[Rinze]

Taupirho,

thanks for your reply.
The csv-file uses ; as delimiter. First we were using TAB as delimiter. It gave the same problem. We've changed to ;, but it didn't make any difference.
Should we swap to ',' anyway? Is that causing the problem?

Thanks
Rinze

 

[taupirho]

First things first, if the fields aren't delimited by commas, it's not (by definition) a CSV. That is by the by however, you need to check you are dealing with a proper text file. Can you output the 1st line of the file you are processing. Also what's in variable strWaarde1 after your read the first line. Another thing to try is just a create a small test CSV file using notepad and see if your program can read that. If it can then there's somethuing suspect with your original input file.


In order to understand recursion, you must first understand recursion.

 

[Rinze]

Taupirho,

thanks for the reply. I'll try to answer. However I think the problem is in the csv, which was added to my first post. Would you mind trying the code with that csv-file?

My actions in the meantime were:
My collegae changed the outpu-file to comma-seperated. I changed the code ("Split"-part), but this didn't make any difference.
The first output (from Wscript.eco) I get is "ÿþR" and that is the main reason I think the csv is somewhat 'corrupted'.

Any csv-file I open through explorer is opened in Excel by the way. The file-type-description is "CSV-bestand van Microsoft Office Excel".
Whenever I create a csv-file with data in the folder using rightclick > New > Textfile, and I put the data in it and name the file 'test.csv', the file is read correct by the script. So, that's a reason to why I think there's something wrong with the csv-file which is generated by Cognos8.
Still.. how can I find out what is wrong with it?

Rinze

 

[taupirho]

When I looked at your input file using notepad there were a number of strange characters. Now these could either be non-ascii characters (in which case you need to get the file fixed) or perhaps the file was produced on a UNIX box say and needs to be converted to DOS format using something like unix2dos utility.


In order to understand recursion, you must first understand recursion.

 

[tsuji]

Your data file is prepared in unicode. You have to give instruction to the opentextfile() accordingly.

>set objTextfile = fs.OpenTextFile("\\ict01\RevalidantmapAansturing\Databases\test.csv",1)
[tt]set objTextfile = fs.OpenTextFile("\\ict01\RevalidantmapAansturing\Databases\test.csv",1,[blue]false[/blue],[red]-1[/red])[/tt]

 

[Rinze]

Thanks a lot Tsuji,

works ok now!!