Importing Text file Issue

[Guest]

Sorry to post this seemingly common problem. I've read and read and tried
various things but still need help. I have an Access 2003 database. I am
need to import multiple comma delimited texts. I can do this very easily
with TransferText.

The problem I have is that I need to extract the first 5 characters of the
file name and the Windows system "Modified" date to add these 2 fields to the
record.
I tried looping through each line of the text file but for some reason, it
isn't recognizing the End of Line character (which is OA I think). I also
tried opening the file to import it into Excel and save it temporarily. Then
import the Excel but of course that causes leading zeros to drop off.

Is there another approach? I saw an interesting post by John Nurick
suggesting using a query such as

SELECT * FROM
[Excel 8.0;HDR=Yes;IMEX=1;database=C:\folder\file.xls;].[sheet1$];

However, I'm not familiar with this method and whether I could use this to
extract the extra 2 fields I need.

Any suggestions?

Thanks
LeAnn

 

[Guest]

Unless there is something unusual about the coding of your text file, the end
of line for text files is usually an ASCII 13 and an ASCII 10. VBA provides
two different constants that equal that value. vbNewLine and vbCrLf. The
both equate to CHR(13) & CHR(10).

The code you posted from John Nurik is used to connect to the first
worksheet of an xls file and link it as a table. I don't know if this will
do in your case.

As to the first five characters of the file name and the date you mention, I
don't think I fully understand the question.

 

[John Nurick]

Hi LeAnn,

As you say, one way is to write code to build and execute a SQL
statement. For a CSV file with field names in the first line, it will be
something like this:

INSERT INTO MyTable (Field1, Field2, First5, TheDate)
SELECT Field1, Field2, "ABCDE" AS First5,
Format(Date(), "YYYYMMDD") AS TheDate
FROM [Text;HDR=Yes;database=C:\Folder].[filename#csv];

If there's no header row, put HDR=No and use default field names F1,
F2... in the SELECT clause.

Sorry to post this seemingly common problem. I've read and read and tried
various things but still need help. I have an Access 2003 database. I am
need to import multiple comma delimited texts. I can do this very easily
with TransferText.

The problem I have is that I need to extract the first 5 characters of the
file name and the Windows system "Modified" date to add these 2 fields to the
record.
I tried looping through each line of the text file but for some reason, it
isn't recognizing the End of Line character (which is OA I think). I also
tried opening the file to import it into Excel and save it temporarily. Then
import the Excel but of course that causes leading zeros to drop off.

Is there another approach? I saw an interesting post by John Nurick
suggesting using a query such as

SELECT * FROM
[Excel 8.0;HDR=Yes;IMEX=1;database=C:\folder\file.xls;].[sheet1$];

However, I'm not familiar with this method and whether I could use this to
extract the extra 2 fields I need.

Any suggestions?

Thanks
LeAnn

 

[Guest]

Thanks for your response. I've tested each of the constants vbLF, vbCR and
vbCRLF and vbNewLine using the following:

strFile = Dir(strPDir)
strPath = strPDir & strFile

Do Until strFile = vbNullString
strPath = strPDir & strFile
strInstr = Left(strFile, 5)

lngFN = FreeFile()
Open strPath For Input As #lngFN

Do Until EOF(lngFN)
Line Input #lngFN, strLine
If Right(strLine, 1) = vbLf Then
Debug.Print strLine

It seems to recognize the vbLF but the entire text file is printed not just
one line. Is there something wrong with my code?

I guess one alternative solution to this is to just use the TrasnferText
(which works) and have a default yes/no field set to yes. Then I can follow
with an update statement to update each of the flagged records with the file
name (instrument number is the first 5 characters of the file name) and the
modified date and unflag the records.

Let me know if there's something wrong in my logic.

Unless there is something unusual about the coding of your text file, the end
of line for text files is usually an ASCII 13 and an ASCII 10. VBA provides
two different constants that equal that value. vbNewLine and vbCrLf. The
both equate to CHR(13) & CHR(10).

The code you posted from John Nurik is used to connect to the first
worksheet of an xls file and link it as a table. I don't know if this will
do in your case.

As to the first five characters of the file name and the date you mention, I
don't think I fully understand the question.

--
Dave Hargis, Microsoft Access MVP

Sorry to post this seemingly common problem. I've read and read and tried
various things but still need help. I have an Access 2003 database. I am
need to import multiple comma delimited texts. I can do this very easily
with TransferText.

The problem I have is that I need to extract the first 5 characters of the
file name and the Windows system "Modified" date to add these 2 fields to the
record.
I tried looping through each line of the text file but for some reason, it
isn't recognizing the End of Line character (which is OA I think). I also
tried opening the file to import it into Excel and save it temporarily. Then
import the Excel but of course that causes leading zeros to drop off.

Is there another approach? I saw an interesting post by John Nurick
suggesting using a query such as

SELECT * FROM
[Excel 8.0;HDR=Yes;IMEX=1;database=C:\folder\file.xls;].[sheet1$];

However, I'm not familiar with this method and whether I could use this to
extract the extra 2 fields I need.

Any suggestions?

Thanks
LeAnn

 

[Guest]

I think I would explore John's suggestion.

But, have you determined what t he character actuall is?
The LineInput # sees Chr(13) or the combination Chr(13) & chr(10) as the end
of a line. It will drop the line terminator. If it is reading in the entire
text file, then there is not a Chr(13). Chr(13) is vbCr.

What produces the text file? Is it possible it doesn't have a standard end
of line delimiter?

--
Dave Hargis, Microsoft Access MVP

Thanks for your response. I've tested each of the constants vbLF, vbCR and
vbCRLF and vbNewLine using the following:

strFile = Dir(strPDir)
strPath = strPDir & strFile

Do Until strFile = vbNullString
strPath = strPDir & strFile
strInstr = Left(strFile, 5)

lngFN = FreeFile()
Open strPath For Input As #lngFN

Do Until EOF(lngFN)
Line Input #lngFN, strLine
If Right(strLine, 1) = vbLf Then
Debug.Print strLine

It seems to recognize the vbLF but the entire text file is printed not just
one line. Is there something wrong with my code?

I guess one alternative solution to this is to just use the TrasnferText
(which works) and have a default yes/no field set to yes. Then I can follow
with an update statement to update each of the flagged records with the file
name (instrument number is the first 5 characters of the file name) and the
modified date and unflag the records.

Let me know if there's something wrong in my logic.

Unless there is something unusual about the coding of your text file, the end
of line for text files is usually an ASCII 13 and an ASCII 10. VBA provides
two different constants that equal that value. vbNewLine and vbCrLf. The
both equate to CHR(13) & CHR(10).

The code you posted from John Nurik is used to connect to the first
worksheet of an xls file and link it as a table. I don't know if this will
do in your case.

As to the first five characters of the file name and the date you mention, I
don't think I fully understand the question.

--
Dave Hargis, Microsoft Access MVP

Sorry to post this seemingly common problem. I've read and read and tried
various things but still need help. I have an Access 2003 database. I am
need to import multiple comma delimited texts. I can do this very easily
with TransferText.

The problem I have is that I need to extract the first 5 characters of the
file name and the Windows system "Modified" date to add these 2 fields to the
record.
I tried looping through each line of the text file but for some reason, it
isn't recognizing the End of Line character (which is OA I think). I also
tried opening the file to import it into Excel and save it temporarily. Then
import the Excel but of course that causes leading zeros to drop off.

Is there another approach? I saw an interesting post by John Nurick
suggesting using a query such as

SELECT * FROM
[Excel 8.0;HDR=Yes;IMEX=1;database=C:\folder\file.xls;].[sheet1$];

However, I'm not familiar with this method and whether I could use this to
extract the extra 2 fields I need.

Any suggestions?

Thanks
LeAnn

 

[Guest]

Sounds intriguing but I have a couple of questions.

This import when triggered will process many files in the folder. Can I use
your example in this situation? It looks like you have know the file name
(they are always different). Also, I need to get the file create date or
last modified date - can your example do that?

I have worked out the code that accomplishes my needs - am testing it right
now. If any one is interested, I can post it.

Thanks
LeAnn

Hi LeAnn,

As you say, one way is to write code to build and execute a SQL
statement. For a CSV file with field names in the first line, it will be
something like this:

INSERT INTO MyTable (Field1, Field2, First5, TheDate)
SELECT Field1, Field2, "ABCDE" AS First5,
Format(Date(), "YYYYMMDD") AS TheDate
FROM [Text;HDR=Yes;database=C:\Folder].[filename#csv];

If there's no header row, put HDR=No and use default field names F1,
F2... in the SELECT clause.

Sorry to post this seemingly common problem. I've read and read and tried
various things but still need help. I have an Access 2003 database. I am
need to import multiple comma delimited texts. I can do this very easily
with TransferText.

The problem I have is that I need to extract the first 5 characters of the
file name and the Windows system "Modified" date to add these 2 fields to the
record.
I tried looping through each line of the text file but for some reason, it
isn't recognizing the End of Line character (which is OA I think). I also
tried opening the file to import it into Excel and save it temporarily. Then
import the Excel but of course that causes leading zeros to drop off.

Is there another approach? I saw an interesting post by John Nurick
suggesting using a query such as

SELECT * FROM
[Excel 8.0;HDR=Yes;IMEX=1;database=C:\folder\file.xls;].[sheet1$];

However, I'm not familiar with this method and whether I could use this to
extract the extra 2 fields I need.

Any suggestions?

Thanks
LeAnn

 

[John Nurick]

To do it this way it's necessary to write VBA code that incorporates the
filename and date into the SQL command. Air code below, requires the
BuildJetTextSource() function which I've posted at the end of this
message:

Dim FolderPath As String
Dim FileName As String
Dim TimeStamp As String
Dim SQLSource As String
Dim SQLCmd As String
Dim dbD As DAO.Database

'Fragments of SQL command
Const SQL_1 = "INSERT INTO MyTable (Field1, Field2, First5, TheDate) "
Const SQL_2 = "SELECT Field1, Field2, "

Set dbD = CurrentDB()

'Get first filename
FolderPath = "D:\Folder\"
FileName = Dir(FolderPath & "*.csv")

Do While Len(FileName) > 0
Timestamp = Format(FileDateTime(FolderPath & FileName), "yyyymmdd")
SQLSource = BuildJetTextSource(FolderPath & FileName, True)

'Assemble SQL statement
SQLCmd = SQL_1 & vbNewLine & SQL_2 _
& "'" & Left(FileName, 5) & "' AS First5, " _
& "'" & TimeStamp & "' AS TheDate " & vbNewLine _
& "FROM " & SQLSource & ";"

'Check that it looks all right; remove once OK
Debug.Print SQLCmd

'Execute it
dbD.Execute SQLCmd, dbFailOnError

'Get next filename
Filename = Dir(FolderPath & "*.csv")

Loop

Sounds intriguing but I have a couple of questions.

This import when triggered will process many files in the folder. Can I use
your example in this situation? It looks like you have know the file name
(they are always different). Also, I need to get the file create date or
last modified date - can your example do that?

I have worked out the code that accomplishes my needs - am testing it right
now. If any one is interested, I can post it.

Thanks
LeAnn

Hi LeAnn,

As you say, one way is to write code to build and execute a SQL
statement. For a CSV file with field names in the first line, it will be
something like this:

INSERT INTO MyTable (Field1, Field2, First5, TheDate)
SELECT Field1, Field2, "ABCDE" AS First5,
Format(Date(), "YYYYMMDD") AS TheDate
FROM [Text;HDR=Yes;database=C:\Folder].[filename#csv];

If there's no header row, put HDR=No and use default field names F1,
F2... in the SELECT clause.

--------------------------------------------------------
Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String

'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' Returns empty string if file not found.
' By John Nurick 2005

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

If Len(Dir(FileSpec)) = 0 Then
'File not found
strTemp = ""

Else
'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"
End If

BuildJetTextSource = strTemp
End Function

 

[Guest]

Very interesting. I tried it but got an error in my Insert statement.
Shouldn't there be a VALUES keyword in the SQL command? I played around with
it but keep getting errors. Here's my latest SQL (and closest attempt):

INSERT INTO Tempe (Field1, Field2, Field3, Field4, Field5, Field6, Field7,
Field8, Field9, Field10, Field11, Field12, Field13, Field14, Field15,
Field16, Field17, Field18, Field19, Field20, Field21, Field22, Field23,
Field24, Field25, Field26, Field27, Field28)
Values('SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7,
Field8, Field9, Field10, Field11, Field12, Field13, Field14, Field15,
Field16, Field17, Field18, Field19, Field20, Field21, Field22, Field23,
Field24, Field25, Field26, "01296" AS Field27, #04/07/19# AS Field28')
FROM [Text;HDR=No;Database=C:\Documents and Settings\015224\Desktop\Prism
Testing\Play\;].[01296_0000301_4_HBcore#txt];

The statement causes an error that says the number of values and destination
fields are not the same

I created my own code that worked but thought maybe yours would be more
efficient. I'm close, what am I mssing?

Thanks

To do it this way it's necessary to write VBA code that incorporates the
filename and date into the SQL command. Air code below, requires the
BuildJetTextSource() function which I've posted at the end of this
message:

Dim FolderPath As String
Dim FileName As String
Dim TimeStamp As String
Dim SQLSource As String
Dim SQLCmd As String
Dim dbD As DAO.Database

'Fragments of SQL command
Const SQL_1 = "INSERT INTO MyTable (Field1, Field2, First5, TheDate) "
Const SQL_2 = "SELECT Field1, Field2, "

Set dbD = CurrentDB()

'Get first filename
FolderPath = "D:\Folder\"
FileName = Dir(FolderPath & "*.csv")

Do While Len(FileName) > 0
Timestamp = Format(FileDateTime(FolderPath & FileName), "yyyymmdd")
SQLSource = BuildJetTextSource(FolderPath & FileName, True)

'Assemble SQL statement
SQLCmd = SQL_1 & vbNewLine & SQL_2 _
& "'" & Left(FileName, 5) & "' AS First5, " _
& "'" & TimeStamp & "' AS TheDate " & vbNewLine _
& "FROM " & SQLSource & ";"

'Check that it looks all right; remove once OK
Debug.Print SQLCmd

'Execute it
dbD.Execute SQLCmd, dbFailOnError

'Get next filename
Filename = Dir(FolderPath & "*.csv")

Loop

Sounds intriguing but I have a couple of questions.

This import when triggered will process many files in the folder. Can I use
your example in this situation? It looks like you have know the file name
(they are always different). Also, I need to get the file create date or
last modified date - can your example do that?

I have worked out the code that accomplishes my needs - am testing it right
now. If any one is interested, I can post it.

Thanks
LeAnn

Hi LeAnn,

As you say, one way is to write code to build and execute a SQL
statement. For a CSV file with field names in the first line, it will be
something like this:

INSERT INTO MyTable (Field1, Field2, First5, TheDate)
SELECT Field1, Field2, "ABCDE" AS First5,
Format(Date(), "YYYYMMDD") AS TheDate
FROM [Text;HDR=Yes;database=C:\Folder].[filename#csv];

If there's no header row, put HDR=No and use default field names F1,
F2... in the SELECT clause.

--------------------------------------------------------
Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String

'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' Returns empty string if file not found.
' By John Nurick 2005

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

If Len(Dir(FileSpec)) = 0 Then
'File not found
strTemp = ""

Else
'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"
End If

BuildJetTextSource = strTemp
End Function

 

[Guest]

Ok I did get it to work. I went back to your code and figured out what I did
wrong. The only issue I have with this method is that it doesn't strip the
text qualifiers. Can this method handle that?

Thanks for enlightening me on a new method for importing.

To do it this way it's necessary to write VBA code that incorporates the
filename and date into the SQL command. Air code below, requires the
BuildJetTextSource() function which I've posted at the end of this
message:

Dim FolderPath As String
Dim FileName As String
Dim TimeStamp As String
Dim SQLSource As String
Dim SQLCmd As String
Dim dbD As DAO.Database

'Fragments of SQL command
Const SQL_1 = "INSERT INTO MyTable (Field1, Field2, First5, TheDate) "
Const SQL_2 = "SELECT Field1, Field2, "

Set dbD = CurrentDB()

'Get first filename
FolderPath = "D:\Folder\"
FileName = Dir(FolderPath & "*.csv")

Do While Len(FileName) > 0
Timestamp = Format(FileDateTime(FolderPath & FileName), "yyyymmdd")
SQLSource = BuildJetTextSource(FolderPath & FileName, True)

'Assemble SQL statement
SQLCmd = SQL_1 & vbNewLine & SQL_2 _
& "'" & Left(FileName, 5) & "' AS First5, " _
& "'" & TimeStamp & "' AS TheDate " & vbNewLine _
& "FROM " & SQLSource & ";"

'Check that it looks all right; remove once OK
Debug.Print SQLCmd

'Execute it
dbD.Execute SQLCmd, dbFailOnError

'Get next filename
Filename = Dir(FolderPath & "*.csv")

Loop

Sounds intriguing but I have a couple of questions.

This import when triggered will process many files in the folder. Can I use
your example in this situation? It looks like you have know the file name
(they are always different). Also, I need to get the file create date or
last modified date - can your example do that?

I have worked out the code that accomplishes my needs - am testing it right
now. If any one is interested, I can post it.

Thanks
LeAnn

Hi LeAnn,

As you say, one way is to write code to build and execute a SQL
statement. For a CSV file with field names in the first line, it will be
something like this:

INSERT INTO MyTable (Field1, Field2, First5, TheDate)
SELECT Field1, Field2, "ABCDE" AS First5,
Format(Date(), "YYYYMMDD") AS TheDate
FROM [Text;HDR=Yes;database=C:\Folder].[filename#csv];

If there's no header row, put HDR=No and use default field names F1,
F2... in the SELECT clause.

--------------------------------------------------------
Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String

'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' Returns empty string if file not found.
' By John Nurick 2005

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

If Len(Dir(FileSpec)) = 0 Then
'File not found
strTemp = ""

Else
'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"
End If

BuildJetTextSource = strTemp
End Function

 

[John Nurick]

If the qualifiers are the "..." used in standard CSV files I'm surprised
they aren't being stripped. If they're anything else, using this method
requires using a schema.ini file (doing the same job as an import/export
specification).

Sorry - no time to say more now; have to get ready to catch a train!

 

[Guest]

They are txt files, not CSV and the qualifiers are '.....' I'll do some
research into schema.ini files. I'll keep using my procedure unitl I have
time to look into this further. Thanks for all your assistance.

Thanks