?
Solved

wrong header from text file

Posted on 2003-03-04
8
Medium Priority
?
406 Views
Last Modified: 2010-04-07
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
Comment
Question by:EDDYKT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8067128
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
 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8067173
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
 
LVL 26

Author Comment

by:EDDYKT
ID: 8069171
Is any way to avoid it like using ini file?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8072825
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
 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8073026
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
 
LVL 3

Expert Comment

by:Da_Weasel
ID: 8073041
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
 
LVL 26

Author Comment

by:EDDYKT
ID: 8074698
>>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
 
LVL 3

Accepted Solution

by:
Da_Weasel earned 400 total points
ID: 8075340
you cannot use period in fieldnames and tablenames.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 3 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question