Global Variables

[mcslemon]

Hi

I'm importing a CSV file into access.

Problem I have is that when the table is saved, it default to certain settings....like Allow Zero Length = No

Is there a setting I can change which stops this? or a macro or something...

Currently I'm going thru manually changing the settings

Neil.

 

[Joe4]

You can design the table with all the fields & properties in Access, and then import your CSV file into this pre-defined table.

 

[mcslemon]

That isn't going to work.

I didn't say last time but I'm creating the layout in Excel because its easier.

I'm saving it as a CSV and then importing it into access, using the first row and the column headers. There is no pre-defined table. Thats the difficulty.

Cheers for the response anyway.

Any other ideas?!

Neil.

 

[mdmilner]

Ok - my first thought is to import the txt file and parse it with VBA.
This would work but it'd be hard to explain fully.

I'm shakey on xls options but this might give somebody here with a lot more xls experience an idea.

What if he imports into two related tables - first with the always present fields and the second with the optional ones (columns) - second might require he import a (named range) pivot table summary of the data that includes the unique key for the first table records.

Mike

 

[Joe4]

If the data is in Excel, there is no need to convert it to a CSV or import. Access can link your Excel file directly to Access as a table. Simply right click your mouse and select "Link Tables".

 

[Joe4]

To clarify the methodology I proposed a little:

The 1st Method I Proposed:
If the format of your table doesn't change, you can create the table structure in Access, setting all of your desired properties. Then you could just import your data into this table every time. Even if you have a header record with field names, you can choose to ignore it.

The advantage to this method is that you only have to setup the table once in Access (not every time).

The 2nd Method I Proposed:
Simply link the Excel table into Access (no need to export/import data). You can control the conditions (i.e. no zero length) in Excel using Validation or macros.

The advantage to this method is that there is no need to import/export your files.


It might help if you explain your process/methodology a little more. Why are you creating the file in Excel, converting it to a CSV then importing it into Access? There appear to be some unnecessary steps there.

 

[mdmilner]

I've noticed that linking tables (xls/mdb) operates differently from imports - and seems to avoid bad field translations (when access assigns the wrong field type for example...and you didn't use a template). Although, it won't always display the values when viewing within Access -- I think they're still there.

Have to fiddle some day and try to dump it into a variant array just to see for myself.



Mike

 

[bat17]

Also with Excel/Access you can just highlight your data, copy it and paste it straight into Access to create a table that way as well.

peter