[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
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
?
345 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

650 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