CSV with greater that 256 Fields

[Guest]

I am evaluating new copiers for our enterprise & one of the things I have to
deal with is account tracking. I want to consider the device’s built in
account tracking apposed to a 3rd party tracking package. One of the
manufacturers in their infantine wisdom they got carried away with the export
file. It’s a CSV file with greater that 256 columns/fields. For each copier
40+ I will have a CSV log with page counts by department. I want only a few
fields out of this file. When I try to import it in Access it displays
perfectly in the Import wizard preview but when the table is created the data
is all shifted & of course it only has 256 fields. Even if I choose SKIP
on all the other fields. I'm not a programmer & don't know VBA. If I pass it
off to our programmers it will never happen. I'm still waiting for them to
parse other data from a year ago :{ I have been able to piece together some
VBS code for windows scripting so I'm not totally inept. I guess I want some
input from someone who knows what they are doing. I don't want to pound my
head against the wall if it can't be done in Access. If I have to parse the
CSV's prior to import does anyone have any suggestions for utility that I can
execute from a command line? If it can be done in VBA could someone give me
some sample code?

 

[John Nurick]

Hi L,

It's possible to write VBA code to do this, but parsing data out of CSV
records can be much more difficult than it sounds, so I usually handle
this sort of situation by using textfile tools to pre-process the text
file into something the standard Access import routine can handle.

If you have, or can install, Perl on your machine (free download from
www.activestate.com among other places) you can do it from the Windows
command line, using something like this:

C:\Temp>perl -wlnMText:arseWords -e"chomp; print join ',',
(parse_line ',', 1, $_ . ',' x 30)[0..5,29,40..80]" "Input.txt"

The list in the square brackets specifies which fields you want:
0 is the first field,
0..5 is the first to sixth fields
and so on.

Once you're happy with the output you can either redirect it to a file
(e.g. append > "Output.txt" to the command) or have Perl edit the file
and leave a backup copy of the original by inserting
-i.bak
before the -e.

 

[Guest]

Other than how to open and close an Excel workbook for automation, I don't
have any code samples that would be directly applicable; however, I can give
you a conceptual description of how it can be done. It will take some
intermediate to advanced VBA skill.

First, open the workbook from Access using automation.
Then you can retrieve data on a cell by cell basis and do whatever you want
with it.
Here is the best example I have. It copies data from a workbook into a
table, then does a sum to compare the values of what is in the spreadsheet
and what is in the table to ensure all the data was imported correctly:

Sub LoadAdjustedActuals()
'D Hargis 5/2005
'Loads the AdjustedActuals Excel spreadsheet into the AdjustedActuals table

Dim rstAccess As Recordset 'Recordset for Access table data
Dim varGetFileName As Variant 'Pass to Common Dialog to open workbook
Dim xlApp As Object ' Reference to Microsoft Excel.
Dim blnExcelWasNotRunning As Boolean ' Flag for final release.
Dim xlBook As Object ' Workbook Object
Dim intLastRow As Integer 'Determines number of rows to import
Dim intRowCount As Integer 'Loops through worksheet rows
Dim intColCount As Integer 'Loops through worksheet columns
Dim dblTotCurMoDollarsAccess As Double 'Accumulates total current month
dollars from Access
Dim dblTotCurMoDollarsExcel As Double 'Accumulates total current month
dollars from Excel
Dim strCurrMonth As String 'Used to build file name to open
Dim strCurrYear As String 'Used to build file name to open
Dim strDefaultDir As String 'Pass Directory to search for common dialog
Dim strfilter As String 'Limit common dialog search to excel workbooks
Dim lngFlags As Long 'Hide readonly check box on common dialog
Dim strCurrDollarsRange As String 'Build range for checking sum

'Set Error Handling
On Error GoTo LoadAdjustedActuals_Err

DoCmd.Hourglass True
DoCmd.SetWarnings False

'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Hides the Read Only Check Box on the Dialog box
lngFlags = ahtOFN_HIDEREADONLY

strCurrMonth = Me.cboPeriod.Column(1)
strCurrYear = Me.txtCurrYear
'Get the File Name To Save
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
varGetFileName = "Vought Invoice " & strCurrMonth & " " & strCurrYear &
".xls"

varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
strDefaultDir, "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Import Adjusted Actuals", , True)
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If

'Open the Table
CurrentDb.Execute "DELETE * FROM AdjustedActuals"
Set rstAccess = CurrentDb.OpenRecordset("AdjustedActuals")

'Open Excel
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xlBook.Worksheets("Actuals_res_export").Activate
ActiveSheet.Range("F3").Select
Selection.End(xlDown).Select
intLastRow = Selection.Row
If intLastRow = 0 Then
MsgBox "No Data to Import" & vbNewLine & "Spreadsheet may be open by
another user", _
vbExclamation + vbOKOnly, "Import Adjusted Actuals"
GoTo LoadAdjustedActuals_Exit
End If

'Start the Loop
For intRowCount = 3 To intLastRow
rstAccess.AddNew
For intColCount = 6 To 42
rstAccess.Fields(intColCount - 6) = _
IIf(intColCount < 26, ActiveSheet.Cells(intRowCount,
intColCount), _
Nz(ActiveSheet.Cells(intRowCount, intColCount), 0))
Next intColCount
rstAccess.Update
Next intRowCount
Me.txtAccessDollars = DSum("[CURRENT MO $'s]", "AdjustedActuals")
Me.txtAccessRows = rstAccess.RecordCount
strCurrDollarsRange = "AP3:AP" & CStr(intLastRow)
Me.txtXlDollars =
xlApp.WorksheetFunction.Sum(ActiveSheet.Range(strCurrDollarsRange))
Me.txtXlRows = intLastRow - 2
MsgBox "Import Complete", vbExclamation + vbOKOnly, "Import Adjusted
Actuals"

LoadAdjustedActuals_Exit:
'Close files and delete link to spreadsheet
On Error Resume Next
xlBook.Close
Set xlBook = Nothing
'If we createed a new instance of Excel
If blnExcelWasNotRunning = True Then
xlApp.Application.Quit
End If
Set xlApp = Nothing
rstAccess.Close
Set rstAccess = Nothing
DoCmd.Hourglass False
Exit Sub

LoadAdjustedActuals_Err:
If Err = 462 Then
MsgBox "Lost Connection with Excel Spreadsheet" & vbNewLine & _
"Close and Re-Open Access", vbExclamation + vbOKOnly,
"ImportAdjusted Actuals"
Else
MsgBox Error$
End If
Resume LoadAdjustedActuals_Exit
End Sub
*************************************
Here is the code necessary for the DetectExcel call. It should be put into
a standard module

Option Compare Database
Option Explicit

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal Hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long


Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim Hwnd As Long
' If Excel is running this API call returns its handle.
Hwnd = FindWindow("XLMAIN", 0)
If Hwnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage Hwnd, WM_USER + 18, 0, 0
End If
End Sub

 

[John Nurick]

Hi Klatuu,

I think maybe you meant this answer for another question!

Other than how to open and close an Excel workbook for automation, I don't
have any code samples that would be directly applicable; however, I can give
you a conceptual description of how it can be done. It will take some
intermediate to advanced VBA skill.

First, open the workbook from Access using automation.
Then you can retrieve data on a cell by cell basis and do whatever you want
with it.
Here is the best example I have. It copies data from a workbook into a
table, then does a sum to compare the values of what is in the spreadsheet
and what is in the table to ensure all the data was imported correctly:
[snip]

I am evaluating new copiers for our enterprise & one of the things I have to
deal with is account tracking. I want to consider the device’s built in
account tracking apposed to a 3rd party tracking package. One of the
manufacturers in their infantine wisdom they got carried away with the export
file. It’s a CSV file with greater that 256 columns/fields. For each copier
40+ I will have a CSV log with page counts by department. I want only a few
fields out of this file. When I try to import it in Access it displays
perfectly in the Import wizard preview but when the table is created the data
is all shifted & of course it only has 256 fields. Even if I choose SKIP
on all the other fields. I'm not a programmer & don't know VBA. If I pass it
off to our programmers it will never happen. I'm still waiting for them to
parse other data from a year ago :{ I have been able to piece together some
VBS code for windows scripting so I'm not totally inept. I guess I want some
input from someone who knows what they are doing. I don't want to pound my
head against the wall if it can't be done in Access. If I have to parse the
CSV's prior to import does anyone have any suggestions for utility that I can
execute from a command line? If it can be done in VBA could someone give me
some sample code?

 

[Guest]

No, his question ended with a request for VBA code that would allow him to
get the data out of the spreadsheet into Access even though there are more
fields than allowed. This code, with modifications, will do that.

Did I miss something?

Hi Klatuu,

I think maybe you meant this answer for another question!

Other than how to open and close an Excel workbook for automation, I don't
have any code samples that would be directly applicable; however, I can give
you a conceptual description of how it can be done. It will take some
intermediate to advanced VBA skill.

First, open the workbook from Access using automation.
Then you can retrieve data on a cell by cell basis and do whatever you want
with it.
Here is the best example I have. It copies data from a workbook into a
table, then does a sum to compare the values of what is in the spreadsheet
and what is in the table to ensure all the data was imported correctly:
[snip]

I am evaluating new copiers for our enterprise & one of the things I have to
deal with is account tracking. I want to consider the device’s built in
account tracking apposed to a 3rd party tracking package. One of the
manufacturers in their infantine wisdom they got carried away with the export
file. It’s a CSV file with greater that 256 columns/fields. For each copier
40+ I will have a CSV log with page counts by department. I want only a few
fields out of this file. When I try to import it in Access it displays
perfectly in the Import wizard preview but when the table is created the data
is all shifted & of course it only has 256 fields. Even if I choose SKIP
on all the other fields. I'm not a programmer & don't know VBA. If I pass it
off to our programmers it will never happen. I'm still waiting for them to
parse other data from a year ago :{ I have been able to piece together some
VBS code for windows scripting so I'm not totally inept. I guess I want some
input from someone who knows what they are doing. I don't want to pound my
head against the wall if it can't be done in Access. If I have to parse the
CSV's prior to import does anyone have any suggestions for utility that I can
execute from a command line? If it can be done in VBA could someone give me
some sample code?

 

[John Nurick]

The OP was asking about CSV files with more than 256 fields, and your
reply was about Excel worksheets, which have only 256 columns. Maybe I'm
missing something, but I can't see how to apply the one to the other.

No, his question ended with a request for VBA code that would allow him to
get the data out of the spreadsheet into Access even though there are more
fields than allowed. This code, with modifications, will do that.

Did I miss something?

Hi Klatuu,

I think maybe you meant this answer for another question!

Other than how to open and close an Excel workbook for automation, I don't
have any code samples that would be directly applicable; however, I can give
you a conceptual description of how it can be done. It will take some
intermediate to advanced VBA skill.

First, open the workbook from Access using automation.
Then you can retrieve data on a cell by cell basis and do whatever you want
with it.
Here is the best example I have. It copies data from a workbook into a
table, then does a sum to compare the values of what is in the spreadsheet
and what is in the table to ensure all the data was imported correctly:
[snip]

:

I am evaluating new copiers for our enterprise & one of the things I have to
deal with is account tracking. I want to consider the device’s built in
account tracking apposed to a 3rd party tracking package. One of the
manufacturers in their infantine wisdom they got carried away with the export
file. It’s a CSV file with greater that 256 columns/fields. For each copier
40+ I will have a CSV log with page counts by department. I want only a few
fields out of this file. When I try to import it in Access it displays
perfectly in the Import wizard preview but when the table is created the data
is all shifted & of course it only has 256 fields. Even if I choose SKIP
on all the other fields. I'm not a programmer & don't know VBA. If I pass it
off to our programmers it will never happen. I'm still waiting for them to
parse other data from a year ago :{ I have been able to piece together some
VBS code for windows scripting so I'm not totally inept. I guess I want some
input from someone who knows what they are doing. I don't want to pound my
head against the wall if it can't be done in Access. If I have to parse the
CSV's prior to import does anyone have any suggestions for utility that I can
execute from a command line? If it can be done in VBA could someone give me
some sample code?

 

[Guest]

DOH!
Sorry, I did miss something. Brain dead.
You can use automation for a csv file as if it were an excel file, but the
256 limit still applies.
My bad

The OP was asking about CSV files with more than 256 fields, and your
reply was about Excel worksheets, which have only 256 columns. Maybe I'm
missing something, but I can't see how to apply the one to the other.

No, his question ended with a request for VBA code that would allow him to
get the data out of the spreadsheet into Access even though there are more
fields than allowed. This code, with modifications, will do that.

Did I miss something?

Hi Klatuu,

I think maybe you meant this answer for another question!

On Wed, 22 Feb 2006 06:33:27 -0800, Klatuu

Other than how to open and close an Excel workbook for automation, I don't
have any code samples that would be directly applicable; however, I can give
you a conceptual description of how it can be done. It will take some
intermediate to advanced VBA skill.

First, open the workbook from Access using automation.
Then you can retrieve data on a cell by cell basis and do whatever you want
with it.
Here is the best example I have. It copies data from a workbook into a
table, then does a sum to compare the values of what is in the spreadsheet
and what is in the table to ensure all the data was imported correctly:

[snip]

:

I am evaluating new copiers for our enterprise & one of the things I have to
deal with is account tracking. I want to consider the device’s built in
account tracking apposed to a 3rd party tracking package. One of the
manufacturers in their infantine wisdom they got carried away with the export
file. It’s a CSV file with greater that 256 columns/fields. For each copier
40+ I will have a CSV log with page counts by department. I want only a few
fields out of this file. When I try to import it in Access it displays
perfectly in the Import wizard preview but when the table is created the data
is all shifted & of course it only has 256 fields. Even if I choose SKIP
on all the other fields. I'm not a programmer & don't know VBA. If I pass it
off to our programmers it will never happen. I'm still waiting for them to
parse other data from a year ago :{ I have been able to piece together some
VBS code for windows scripting so I'm not totally inept. I guess I want some
input from someone who knows what they are doing. I don't want to pound my
head against the wall if it can't be done in Access. If I have to parse the
CSV's prior to import does anyone have any suggestions for utility that I can
execute from a command line? If it can be done in VBA could someone give me
some sample code?

 

[Guest]

It took longer to install Perl than to count the columns & run the script.
Perl wasn't my first choice & I know the programmers are going to freak...
but if they want to write it in RPG they can have at it!
Thanks John
L
Oh, can I just copy the Perl directory over to another machine or do I have
to actually perform the install on the box that the script will execute on.
I'll give it a try...
Thanks again.

Hi L,

It's possible to write VBA code to do this, but parsing data out of CSV
records can be much more difficult than it sounds, so I usually handle
this sort of situation by using textfile tools to pre-process the text
file into something the standard Access import routine can handle.

If you have, or can install, Perl on your machine (free download from
www.activestate.com among other places) you can do it from the Windows
command line, using something like this:

C:\Temp>perl -wlnMText:arseWords -e"chomp; print join ',',
(parse_line ',', 1, $_ . ',' x 30)[0..5,29,40..80]" "Input.txt"

The list in the square brackets specifies which fields you want:
0 is the first field,
0..5 is the first to sixth fields
and so on.

Once you're happy with the output you can either redirect it to a file
(e.g. append > "Output.txt" to the command) or have Perl edit the file
and leave a backup copy of the original by inserting
-i.bak
before the -e.

I am evaluating new copiers for our enterprise & one of the things I have to
deal with is account tracking. I want to consider the device’s built in
account tracking apposed to a 3rd party tracking package. One of the
manufacturers in their infantine wisdom they got carried away with the export
file. It’s a CSV file with greater that 256 columns/fields. For each copier
40+ I will have a CSV log with page counts by department. I want only a few
fields out of this file. When I try to import it in Access it displays
perfectly in the Import wizard preview but when the table is created the data
is all shifted & of course it only has 256 fields. Even if I choose SKIP
on all the other fields. I'm not a programmer & don't know VBA. If I pass it
off to our programmers it will never happen. I'm still waiting for them to
parse other data from a year ago :{ I have been able to piece together some
VBS code for windows scripting so I'm not totally inept. I guess I want some
input from someone who knows what they are doing. I don't want to pound my
head against the wall if it can't be done in Access. If I have to parse the
CSV's prior to import does anyone have any suggestions for utility that I can
execute from a command line? If it can be done in VBA could someone give me
some sample code?

 

[John Nurick]

The ActivePerl installation makes very few registry entries, and none of
the ones I've seen look vital, so copying the folder could conceivably
work. Maybe there's an answer on the web somewhere.

Meanwhile, I've just noticed the expression
$_ . ',' x 30
in the one-liner I gave you. This is left over from the last job I used
it on, which handled a CSV file with variable number of fields per line.
It used
print join ',', (parse_line ',', 1, $_ . ',' x 30)[0..29]
to take a line (in the variable $_) that might have fewer than 30
fields, pad it out with 30 commas to ensure that it has at least 30
fields, and then return the first 30 fields from the result.

With your data the padding is unnecessary, so you can replace the
$_ . ',' x 30
with just
$_

It took longer to install Perl than to count the columns & run the script.
Perl wasn't my first choice & I know the programmers are going to freak...
but if they want to write it in RPG they can have at it!
Thanks John
L
Oh, can I just copy the Perl directory over to another machine or do I have
to actually perform the install on the box that the script will execute on.
I'll give it a try...
Thanks again.

Hi L,

It's possible to write VBA code to do this, but parsing data out of CSV
records can be much more difficult than it sounds, so I usually handle
this sort of situation by using textfile tools to pre-process the text
file into something the standard Access import routine can handle.

If you have, or can install, Perl on your machine (free download from
www.activestate.com among other places) you can do it from the Windows
command line, using something like this:

C:\Temp>perl -wlnMText:arseWords -e"chomp; print join ',',
(parse_line ',', 1, $_ . ',' x 30)[0..5,29,40..80]" "Input.txt"

The list in the square brackets specifies which fields you want:
0 is the first field,
0..5 is the first to sixth fields
and so on.

Once you're happy with the output you can either redirect it to a file
(e.g. append > "Output.txt" to the command) or have Perl edit the file
and leave a backup copy of the original by inserting
-i.bak
before the -e.

I am evaluating new copiers for our enterprise & one of the things I have to
deal with is account tracking. I want to consider the device’s built in
account tracking apposed to a 3rd party tracking package. One of the
manufacturers in their infantine wisdom they got carried away with the export
file. It’s a CSV file with greater that 256 columns/fields. For each copier
40+ I will have a CSV log with page counts by department. I want only a few
fields out of this file. When I try to import it in Access it displays
perfectly in the Import wizard preview but when the table is created the data
is all shifted & of course it only has 256 fields. Even if I choose SKIP
on all the other fields. I'm not a programmer & don't know VBA. If I pass it
off to our programmers it will never happen. I'm still waiting for them to
parse other data from a year ago :{ I have been able to piece together some
VBS code for windows scripting so I'm not totally inept. I guess I want some
input from someone who knows what they are doing. I don't want to pound my
head against the wall if it can't be done in Access. If I have to parse the
CSV's prior to import does anyone have any suggestions for utility that I can
execute from a command line? If it can be done in VBA could someone give me
some sample code?