[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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

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
donhannam
Asked:
donhannam
  • 3
  • 2
1 Solution
 
Kelvin SparksCommented:
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
 
NatchiketCommented:
0
 
donhannamAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
NatchiketCommented:
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
 
NatchiketCommented:
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
 
donhannamAuthor Commented:
Thanks

Thats what I was after - works perfect
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now