Re: How to ignore extra or missing columns?

John Nurick wrote:
> 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.

Not really. A given file will have a fixed number of columns, it is
just that each file may have a different number. And, I have no real
control over this variation scenario. It is what is given to me.

>
> 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

Perhaps. However, some columns contain embedded commas. Example:

"1234","Simpson, OJ","123 Maple St.","CA"
"6544","Gates, Bill","One Microsoft Way","WA"
etc...

Here the name column (second) has an embedded comma. The Split function
will mess that up.

However, at least it is something to consider and at least I know there
is probably no obvious built-in way to do it without some somewhat
involved programming.

Thanks for your feedback and suggestions,

-T-

>
>
> 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 Access MVP] ...
    (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)
  • Re: How to open a .CSV file ?
    ... I suppose by your reasoning every file is just a big long string and we should all be using the string manipulation functions to work with them. ... programming possibilites. ... 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)