Member Avatar for Seba Sama

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
  • 170 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by ChrisPadgham
Member Avatar for ChrisPadgham

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.

Member Avatar for Seba Sama

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?

Member Avatar for ChrisPadgham

Possibly change the extension on the file before you open it. You can do this using the FileSystemObject

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.