0

Hello!

I need to import some data from a .csv file, process it and write results in an Excel file.
So... I use ADODB recordsets and Microsoft Text Driver:

Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim cnStr As String

    cnStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
        & filePath & ";Extensions=csv,txt;"

    cn.Open cnStr

    Set rs = New ADODB.Recordset
    rs.Open "select * from " & fileName & " " _
        & "", cn, adOpenStatic, adLockOptimistic, 1

All works ok BUT the csv file <fileName> contains some HEX data(.csv file is generated from a EPROM dump file) which the query replaces with ""

This is csv file:

id043_AAdParIsa1				40	7E	7F	7E	BE	FC	1	0	40	7E	7F	7E	BE	FC	1	0
id043_AAdParIsa1				40	7E	7F	7E	BE	FC	1	0	40	7E	7F	7E	BE	FC	1	0
id043_AAdParIsa1				40	7E	7F	7E	BE	FC	0	0	0	FF	0	0	0	FF	0	0
id043_AAdParIsa1				0	FF	0	0	0	FF	0	0	0	FF	0	0	0	FF	0	0
id043_AAdParIsa1				0	0	0	0	0	0	0	0	2	40	40	3	0	0	0	0
id043_AAdParIsa1				0	0	0	14	E0	18	30	2	0	0	0	0	2	0	40	1

And this is my recordset:

id043_AAdParIsa1				40						1	0	40						1	0
id043_AAdParIsa1				40						1	0	40						1	0
id043_AAdParIsa1				40						0	0	0		0	0	0		0	0
id043_AAdParIsa1				0		0	0	0		0	0	0		0	0	0		0	0
id043_AAdParIsa1				0	0	0	0	0	0	0	0	2	40	40	3	0	0	0	0
id043_AAdParIsa1				0	0	0	14		18	30	2	0	0	0	0	2	0	40	1

I don't know why... but if there is 1 or 2 characters... i just get the "". Anyone knows why is this happening? I tried querying in another sheet of the same workbook with the code.... any FF, 1C, 4E or even u ( lcase("U")) is read as "". BUT NOT THE "x" !!!!
I have to import data from 2 files, process it, compare results and then export a table. I don't want to iterate trough 4096 lines, cell by cell, to find what I need.

Any help would be appreciated! Thanks in advance!

Seba Sama

2
Contributors
3
Replies
6
Views
6 Years
Discussion Span
Last Post by ChrisPadgham
0

It looks like you are storing the values in a numeric field and it is trying to interpret the values as decimal numbers. Change your field definitions to "Text" and it should bring everything in OK.

0

Actually the csv file has the general format applied. That is something I can't change, being provided to me by an automation. It seems that there is a problem when I try to change the csv format as text: excel prompts me "click yes to keep formating....may be incompatible with...." then I click yes but it keeps the general format.
If I manualy open the csv and save it as txt then it reads ok. But this would not solve the problem.
Is there a way to open csv as txt file?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.