Solved

importing a txt / csv file - not picking up the first line - no header line in file

Posted on 2008-06-17
6
321 Views
Last Modified: 2011-09-20
I am using the following code to import a text file which works great except that the first line does not import.

I am sure the reason is the first line is treated as a header and am sure there is a way to import without header such as HDR=No by cannot find any help on correct way to do this - appreciate any ideas:-

Set Importcon = New ADODB.Connection
   
    Importcon.Open "Driver={Microsoft Text Driver (*.txt; *.csv)}; DataSource=" & strFilePath

        Set rst = New ADODB.Recordset
        strImportSQL = "SELECT * FROM " & strFileName
        rst.Open strImportSQL , Importcon

....
0
Comment
Question by:donhannam
[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
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21801249
I general use the transfertext command and import to a temp table. You can then dictate whether  there is a header row (delimiters etc)
0
 
LVL 17

Expert Comment

by:Natchiket
ID: 21801267
0
 

Author Comment

by:donhannam
ID: 21801423
Thanks.

Have used transfer text in past and need to set specification which I have had trouble with.

Connection strings site looks promising - did not see a way to get first line in on the first entry and could not get other methods of connection to work - I am importing in adp access database to MSSQL.

Appreciate any help with full method of connection

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 17

Accepted Solution

by:
Natchiket earned 500 total points
ID: 21801609
Hi have successfully tried it like this

Function TestText()
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\PCT;Extended Properties='text;HDR=No;FMT=Delimited';"
Set cn = New ADODB.Connection
cn.ConnectionString = strConn
cn.Open
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM hello.txt", cn, adOpenStatic, adLockReadOnly
Do Until rst.EOF
    Debug.Print rst(0)
    rst.MoveNext
Loop

End Function
0
 
LVL 17

Expert Comment

by:Natchiket
ID: 21801731
If it still doesn't work, it may be that the PC doesn't have the correct mdac.  My *guess* is that dao36.dll needs to be installed since the method uses jet.  otherwise trying installing the latest mdac components e.g.
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&displaylang=en
0
 

Author Closing Comment

by:donhannam
ID: 31467900
Thanks

Thats what I was after - works perfect
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

756 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