Using Open & Input as #1 to import CSV file

[TimGoff]

Hi,

I'm attempting to import a CSV file, which has a header, and the data in each cell is also surrounded with "".

I'm using -

Open strPathName & strFilename For Input As #1
Do While Not EOF(1)
Input #1, strAssetNumber, strTypeLoop, etc
Close #1

This works fine, but what I want to do is:

a) Miss out the header record (ie the titles)
b) Skip some of the cells on each line

Any ideas how i do this?

Thanks in advance
Tim

 

[RobBroekhuis]

example:

Open strPathName & strFilename For Input As #1
line input#1, strDummy
Do While Not EOF(1)
Input #1, strAssetNumber, strDummy,strDummy,strTypeLoop, strDummy
loop
Close #1

This ignores one header line (the line input statement), then takes the 1st and 4th items on each line, ignoring the 2nd, 3rd and 5th ones. Modify as needed for your situation.
Rob

 

[TimGoff]

Cool, cheers Rob

Anyway of ignoring the entire first line?

 

[RobBroekhuis]

Tim,
That's what the line input#1 does - it takes the entire first line of the file.
Rob

 

[TimGoff]

Hi Rob,

Is there anyway of telling it to ignore line 1, and start at line 2?

Also, another problem i have (!)...

Some looks like this...

,"Data"data",

instead of...

,"Datadata",

Is there anyway I can get round this?

Thanks
Tim

 

[SBendBuckeye]

Tim,

Line Input reads an entire line from the file so the one before the do while not eof reads it and then does nothing with it which is the same thing as skipping it.

If you don't need the double quotes, you can use the replace function in Access2000 and above to get rid of them.

var1 = Replace(var1, """", vbNullString)

You need 2 " to indicate a single double quote since it is the standard delimiter and then you need to put that within double quotes which is why you need 4 together above.

Good Luck!

 

[RobBroekhuis]

Tim,
If the quotation marks are giving you trouble, please explain the problem (error message? wrong behavior?). We'll be able to suggest a solution.
Did you understand SBendBuckeye's further explanation of the line input statement? (Thanks SBB)

Rob

 

[TimGoff]

Hi guys

Sorry its taken a while to come back to you.

I understood the line input part - my fault not looking properly!

On the other point - the double quotes. The Input command appears to strip off the double quotes around the data leaving just the data to be set to the variable - but when there is a quote within the double quote it throws out the whole process - ie the quote in the middle becomes the start of the next cell - does that make any sense?

Thanks
Tim

 

[RobBroekhuis]

Sequential input using input# won't successfully read a file with loose quotes, I believe. Are you using XL2000 or better? If so, you can use instead

Line input#1,strLine
strArray=split(strLine,",&quot
strAssetNumber=strArray(0)
strTypeLoop=strArray(3)

the above is just an example, where you are interested in the first and fourth comma-separated values from each line. The split function, which does not exist, I believe, in XL97 and below, isn't bothered by single quotes.

Rob

 

[TimGoff]

Cheers Rob, I'll have a look over the weekend

Tim

 

[TimGoff]

Hi Rob,

Great, that does the job.

One other thing, how would you remove the quotes around each piece of data now before importing it into the database?

Thanks
tim

 

[RobBroekhuis]

Tim,
If you have a single quote right at the front and the end of the string, then you can use something like:

strTypeLoop=mid(strTypeLoop,2,len(strTypeLoop)-2)

If you sometimes have spaces before or after the quotes, then you'll have to use the trim() function to remove them beforehand:

strTypeLoop=trim(strArray(3))
strTypeLoop=mid(strTypeLoop,2,len(strTypeLoop)-2)

Rob