asked on

Interesting problem wile reading text file in VBA

Hi Experts,

I have the following code that reads a CSV file and imports the contents into a SQL table

    If Len(sCriteria) = 0 Then
        conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\Application;"
        rs.Open "SELECT * FROM [Applications.csv]", conn, adOpenStatic, adLockReadOnly, adCmdText
    Else
        rs.Open "Select * from EmployeesWebImports where " & sCriteria, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    End If
       
    rs.MoveFirst
    
      
    While Not rs.EOF
'My code here........
debug.print rs.fields("ip_address")

Open in new window

Now all fields are working fine, however the ip address is somehow being misread and cut off, for example I have the ip address 66.222.77.111 and the VBA code reads it as 66.2227

Any idea why is that and how to fix it?

Thanks in advance.

Gustav Brock

Use DAO and create an import specification for the file (use the wizard) where you specify this field as text. Then link the file.
Right now, it is read as a number because it is not wrapped in double-quotes in the CSV file.

/gustav

bfuchs

ASKER

@Gustav,

Why exactly is it being read as a number, since they all have text inside, the ".", will an address field also be considered as number just because the first character or two are numbers?

And why is it being cut off
, the fact that it eliminates the "." would not be such a great deal as I can fix it by code.

Regarding the import specification, I have an issue, as its not always the same fields and formats..

how about if I first open the file in code for editing and wrap that field in double-quotes?

Thanks,
Ben

bfuchs

ASKER

@Gustav,

Any other suggestion for my problem?

btw, I tried manually inserting single quotes into that field but that didn't alter the code behavior.

Thanks,
Ben

Gustav Brock

> .. it's not always the same fields and formats.

Then you will have to open it as a text file and parse a line to read and interpret the structure.
Or have some manual handling for each file.

> .. if I first open the file in code for editing and wrap that field in double-quotes?

That should work.

> .. I tried manually inserting single quotes into that field but that didn't alter the code behavior.

No, it is double-quotes or nothing. Single-quotes would require an import specification.

/gustav

PatHartman

If you link the file and then open it in DS view, do you still have the problem?
Have you actually looked at the .csv file with a text editor to see the values in plain text?

Jeffrey Coachman

Some systems I have seen to do this treat each "octet" as separate fields.
Then conversion, Import/export is straightforward.

Then you can simply concatenate the fields to "display" the IP address with the correct format.

In any event,, FWIW, a csv file like this comes into Access directly with the IP address correctly formatted
IP.csv

bfuchs

ASKER

@Gustav,

> .. if I first open the file in code for editing and wrap that field in double-quotes?

That should work.

Actually I tested manually and that works.
Do you have an example of code that performs the following, opens an existing CSV file, looks for the column named ip_address and wraps it in double quotes, or perhaps something that demonstrates your first suggestion?

@Pat,
Its an ADP and linking to files its not relevant there, besides that file imported does not always have the same columns.

File in notepad displays the IP address normal.

@Jeff,

Some systems I have seen to do this treat each "octet" as separate fields.

What are you referring to, third party software solutions?

Tested the code with your file and the same happens there, see attached.

Thanks,
Ben
Untitled.png

Jeffrey Coachman

What are you referring to, third party software solutions?

No, just the way the data is stored in the table...

But lets see if someone can resolve your issue as posted.
Seems like you just need to tweak the code a bit...

Jeff

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

Hi Gustav,

I tested your code of reading line by line and that worked,

However since this solution seems to be kind of a task (for me..)
Just want to make sure this is my only option & there is not an easier way out here (like Jeff calls it, to tweak the code a bit)..?

Wondering why all programs interprets this IP field correct, Excel, Notepad & your code as well..except the existing code I have in place?!

Thanks,
Ben

Gustav Brock

Good question.

But - as the files don't follow the same schema - I see no way other than a custom solution.
Have in mind also, that reading the file directly adding records line by line should be quite fast.

/gustav

bfuchs

ASKER

Actually I tested your code with that in mind, but soon realized that this method will require redesign the entire code, as I cannot refer to them as a field collection.

Since the original code is using specific driver

conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\Application;"

Open in new window

Do you think by using a different driver may solve this issue?

Thanks,
Ben

Gustav Brock

I don't think any other driver exists.

But if you had quotes around the IP-address, didn't it work?

If so, you could do as described - read the file and create a new with the fields reordered and the IP-address quoted. And your other code would remain unchanged.

/gustav

PatHartman

Text fields were not quoted in the sample posted.
It is really too bad that there is no hard and fast standard for .csv files. There are some suggestions but way too many people deviate when writing files. There are also tricks that some people implement to make the file more friendly for Excel so that Excel doesn't convert long numeric strings to scientific notation or remove leading zeros from zip codes. Of course these tricks, prevent Access from interpreting the file correctly. It looks to me like Access is making an assumption because the IP is not quoted that it is a number and is truncating it. I think there would be no problem if text fields were quoted as the standard "suggests" so if the source can change their export, that would be my choice.

bfuchs

ASKER

@Pat,

There are also tricks that some people implement to make the file more friendly for Excel

This is not caused by any trick implemented by other people, I just tested by creating a new file in Excel and saving it as CSV, and the same problem occurs there.

@Gustav,
Just waiting if someone can come up with a solution that will save me that extra step, however if nothing better is suggested I will definitely consider doing that option.

Thanks,
Ben

PatHartman

Access expects text fields to be enclosed in double quotes. If they are not, Access interprets them and if the first thirty records look like they might be numbers, that is what it assumes. Excel does not conform to this standard - who would guess the apps were written by the same company. It is finally with A2013 that Access can reliably import spreadsheet data.

With a non-conforming file format, you have to read the file using FSO or the built in Read commands and parse it yourself. You can't use canned features such as TransferText since they rely on specific formatting rules.

bfuchs

ASKER

@Pat,
Thanks for the explanation.
From between the lines I get the message that Gustav's solution is the one I have to go with..

bfuchs

ASKER

Ok Experts,

I guess will have to stick to Gustav's solution,

Thanks to all participants,
Ben

Gustav Brock

You are welcome!

/gustav