Re: How to ignore extra or missing columns?

This probably isn't difficult, but it's beyond the capability of the
standard text import mechanism.

The way you describe it, it sounds as if the first few columns define a
record, and the remaining columns contain a variable number of related
items. If so, the best approach would be to parse the CSV file
accordingly and import it into records in two or more related tables.

Otherwise, you can either pre-process the CSV files,to give them all the
desired number of fields, or else write VBA code to read them line by
line and append fields to the table as required.

This snippet of VB/VBA code will take a comma-delimited list of values
and return the first NFields of them, padding with empty values if there
aren't enough in the list. If you remove the As String, As Long, etc. it
will also work in VBScript.

Function Make20(Line As String, NFields As Long) As String
Dim arFields As Variant

arFields = Split(Line, ",", NFields + 1)
If UBound(arFields) <> NFields -1 Then
ReDim Preserve arFields(NFields -1)
End If
Split20 = Join(arFields, ",")
End Function


On 30 Jan 2006 19:40:38 -0800, "topmind" <topmind@xxxxxxxxxxxxxxxx>
wrote:

>I need a way to dynamically process input CSV files with varying
>schemas. I have not found a way to get Access to ignore extra or
>missing columns. For example, suppose I set up a work table with 30
>columns. Sometimes I want it to automatically import a 10-column table
>and other times a 50 column table. The first should simply have nulls
>in the extra 20 and the second one (50 cols) would have the last 20 be
>ignored. It is too picky about knowing the number of columns in
>advanced. I live in a dynamic world :-)
>
>Thanks in advanced for any solutions proposed.
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

.

Relevant Pages

  • Re: How to ignore extra or missing columns?
    ... the best approach would be to parse the CSV file ... > Function Make20(Line As String, NFields As Long) As String ... > John Nurick ...
    (microsoft.public.access.externaldata)
  • Re: CSV file import
    ... Doug Steele, Microsoft Access MVP ... (no private e-mails, please) ... I have managed to import a CSV file into a string but Im wondering if ... The CSV file is comma delimited and only containes one ...
    (microsoft.public.access.externaldata)
  • Re: Inconsistency of Export to a CSV file.
    ... A CSV file is a txt file with a different extension. ... >>Public Sub ExportDelim(strTable As String, ... >> Dim varData As Variant ... >> Dim intFileNum As Integer ...
    (microsoft.public.access.externaldata)
  • Re: type conversion error
    ... Take a look to the missing data in your csv file so you can understand why ... ByVal HDR As Boolean) As String ... Dim strFolder As String ... Dim strFileName As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to open a .CSV file ?
    ... I'm really having trouble understanding why you feel the need to insult ... I suppose by your reasoning every file is just a big long string and we ... I assume when you said "parse at the comma" you meant string.split. ... If I save the .CSV file to an .XLS file, ...
    (microsoft.public.dotnet.languages.vb)