Solved

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

Posted on 2008-06-17
6
328 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Independent Software Vendors: 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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

739 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