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.
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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
bfuchsAuthor Commented:
@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
bfuchsAuthor Commented:
@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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
> .. 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
PatHartmanCommented:
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 CoachmanMIS LiasonCommented:
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
bfuchsAuthor Commented:
@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 CoachmanMIS LiasonCommented:
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
Gustav BrockCIOCommented:
> Actually I tested manually and that works.

Then that seems to me as your only option.
It should be trivial to do:

1. Open the file for read and a new file for write
2. Read the first line
3. Use, say, Split() to read the field names into an array, say, aF
4. Locate field ip_address and its index, say 4,
5. Shuffle the field names to obtain a fixed order your database knows.

Now read line by line from the original and write the lines to the new file:

    aF = Split(OldLine, ",")
    ' Concatenate in new order and modify field 4:
    NewLine = aF(5) & "," & aF(1) & "," & aF(2) & "," & Chr(34) & aF(4) & Chr34 & "," & aF(3)

Finally, close both files.  


Another option is to read the file directly - read line by line and add to your recordset:
=========Reading from a file==========

    Dim dbs As Database
    Dim rst As Recordset
    Dim strRecordSource As String       'Source for recordset, can be SQL, table, or saved query
    Dim intFileDesc As Integer      	'File descriptor for output file
    Dim strSourceFile As String     	'Full path of source file
    Dim strTextLine As String       	'Input buffer
    Dim strField1 As String         	'Extracted Field1 from buffer
    Dim strField2 As String         	'Extracted Field2 from buffer
    Dim strField3 As String         	'Extracted Field3 from buffer

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strRecordSource, dbOpenDynaset)
    
    intFileDesc = FreeFile
    Open strSourceFile For Input As #intFileDesc
    Do While Not EOF(intFileDesc) 	' Loop until end of file.
        Line Input #intFileDesc, strTextLine                  'Read line into buffer
	' <  use string handling to extract the fields from strTextLine
	'    and place them in strField1, strField2, & strField3         >
	rst.AddNew			'depending on your situation, you may want to find an
					'   existing record and Edit instead
	rst!Field1 = strField1
	rst!Field2 = strField2
	rst!Field3 = strField3
	rst.Update
    Loop
    Close #intFileDesc    'Close file.
    rst.Close		  'Close the recordset
    Set rst = Nothing
    Set dbs = Nothing     'Garbage handling before we exit the function

==================================================================================================
=========Writing to a file==========

    Dim dbs As Database
    Dim rst As Recordset
    Dim intFileDesc As Integer      'File descriptor for output file (number used by OS)
    Dim strOutput As String         'Output string for entry
    Dim strRecordSource As String   'Source for recordset, can be SQL, table, or saved query
    Dim strOutfile As String        'Full path to output file

    Kill strOutfile                 'Delete the output file before using it.
                                    'Not necessary, but ensures you have a clean copy every time
    intFileDesc = FreeFile          'Get a free file descriptor
    Open strOutfile For Binary As #intFileDesc 'Open the output file for writing
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strRecordSource ) 'open the recordset based on our source string
    With rst    'make things easier for ourselves
	While Not .EOF
            strOutput = !Field1 & ";" & !Field2 & ";" & !Field3
            Print #intFileDesc, strOutput   'Print output string to file
            .MoveNext   	'Advance to next record in recordset
        Wend
        .Close                  'Close this recordset
    End With
    Close #intFileDesc          'Close output file
    Set rst = Nothing
    Set dbs = Nothing		'Garbage handling before we exit the function

==================================================================================================

Open in new window

This is DAO, but I'm sure you get the idea.

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
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 BrockCIOCommented:
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
bfuchsAuthor Commented:
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 BrockCIOCommented:
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
PatHartmanCommented:
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.
bfuchsAuthor Commented:
@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
PatHartmanCommented:
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.
bfuchsAuthor Commented:
@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..
bfuchsAuthor Commented:
Ok Experts,

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

Thanks to all participants,
Ben
Gustav BrockCIOCommented:
You are welcome!

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.