• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

wrong header from text file

I've txt file and try to read from ADO

text file
Time,SRUHeatX.CO,SRUHeatX.THeater,SRUHeatX.CToluene,SRUHeatX.CHydrogen,SRUHeatX.CHeptane,SRUHeatX.TReactor
2002-04-17 01:02:03,223,889,524.057713540152,2097.23075416061,999.889874332185,739.869192813063

My code

Option Explicit

Private m_cn As ADODB.Connection

Private Sub Form_Load()
 Dim rs As Recordset
  Dim strFilePath As String
  strFilePath = "c:\temp"   ' <-- CHANGE ME
 Dim i As Field
  Dim strFileName As String
  strFileName = "HeatXSimWithDots.txt"    ' <-- CHANGE ME
 
  Set m_cn = New ADODB.Connection
  With m_cn
     .CursorLocation = adUseClient
     .ConnectionString = _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Initial Catalog=" & strFilePath & ";"
     .Open
  End With
 
  Set rs = m_cn.Execute("SELECT * FROM " & strFileName)
With rs
For Each i In rs.Fields
Debug.Print i.Name & "    " & i.Value
Next
End With
'Debug.Print rs.GetString(adClipString, -1, "~", "|")

End Sub


Output

Time    4/17/2002 1:02:03 AM
SRUHeatX#CO    223
SRUHeatX#THeater    889
SRUHeatX#CToluene    524.057713540152
SRUHeatX#CHydrogen    2097.23075416061
SRUHeatX#CHeptane    999.889874332185
SRUHeatX#TReactor    739.869192813063


The question is why the header is change from . to #

ie SRUHeatX.CO to SRUHeatX#CO


How do I solve it.
Please help
0
EDDYKT
Asked:
EDDYKT
  • 6
  • 2
1 Solution
 
Da_WeaselCommented:
You will have to parse the data yourself before displaying it.  It has something to do with the driver.  Any database connection that uses files/folders to represent tables/databases does this.

The funny thing is that when you built the SQL Query with the value "HeatXSimWithDots.txt" (the filename) it worked.  I get errors when I do this, and must form my SQL like this: "SELECT * FROM myfile#txt".  I always use OLE DB for my connection strings though, and you are using ODBC.  There maybe differences between the to and how they handle the data access.
0
 
Da_WeaselCommented:
Why do you have SRUHeatX in front of each one?  Period have special meaning in SQL to show relationships between tables and fields.  The driver must be automatically converting them to a valid value for you.  Just think about how it would look if you were trying to reference the fields implicitly with the table name

HeatXSimWithDots.txt.SRUHeatX.Co

SQL wouldnt now where your table name began and field name ended.

So it converts it to this:

HeatXSimWithDots#txt.SRUHeatX#Co
0
 
EDDYKTAuthor Commented:
Is any way to avoid it like using ini file?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Da_WeaselCommented:
yea, you can use a schema.ini file

make a file called schema.ini and put it in the same dir as the test file.

it content should look something like this.

Version 1:  (text file only contains data, no field headers)

[HeatXSimWithDots.txt]
ColNameHeader=False
CharacterSet=ANSI
Format=CSVDelimited
Col1=CO Date6
Col2=THeater Long
Col3=CToluene Long
Col4=CHydrogen Double
Col5=CHeptane Double
Col6=TReactor Double

You will probably need to customize this schema.ini file to better meet your needs.  It all depends on what kind of data base you are importing this into and what is being done with the data afterwards.

Here is a link to the complete info on what settings are available in a schema.ini file:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp
0
 
Da_WeaselCommented:
oops forgot version2...hehe

Version 2 (text file contains field headers)

[HeatXSimWithDots.txt]
ColNameHeader=True
CharacterSet=ANSI
Format=CSVDelimited
MaxScanRows=0

The MaxScanRows option tells Jet how many records to use to determine the field type.  When set to 0 it scans all rows.  If you text file is large 50,000+ records, then you should set MaxScanRows=100 or 1000 to speed up the process.
0
 
Da_WeaselCommented:
Using version 2 your text file can look like the example in your question, except that you will want to remove the periods from the field names.

Using version 1 your text file should only contain data.
0
 
EDDYKTAuthor Commented:
>>Using version 2 your text file can look like the example in your question, except that you will want to remove the periods from the field names.



That means I cannot use period on the header?
On matter what?
0
 
Da_WeaselCommented:
you cannot use period in fieldnames and tablenames.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now