?
Solved

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

Posted on 2008-06-17
6
Medium Priority
?
338 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 17

Accepted Solution

by:
Natchiket earned 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 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