Global Variables

mcslemon

New Member
Joined
Feb 24, 2003
Messages
14
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,829
Office Version
  1. 365
Platform
  1. Windows
You can design the table with all the fields & properties in Access, and then import your CSV file into this pre-defined table.
 

mcslemon

New Member
Joined
Feb 24, 2003
Messages
14
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

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,829
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,829
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
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.

:biggrin:

Mike
 

bat17

Well-known Member
Joined
Aug 15, 2003
Messages
1,470
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,882
Messages
5,766,896
Members
425,383
Latest member
IllDo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top