xls technique question

[mdmilner]

I may be 'missing' something so I wanted to ask.
In the last year, I've gravitated away from using spreadsheets (xls) as a preferred way to import data into an Access database. The main reason is the amount of control over imported field types that I gain from using specification templates and importing raw text files.

I'm interested in any suggestions as abstract or detailed as you'd like to make it.

Mike

 

[Andrew Fergus]

Hi Mike

I too default to Access where there is a lot of data to be manipulated for a few reasons : mainly speed, error control, exception filtering etc. Is there anything in particular you think you might be "missing"? BTW I entered "importing data into Microsoft Access" into Google and it returned about 800k pages!

Andrew

 

[mdmilner]

What I mean is, given the ability to choose the form of my imported information...my first choice is for it to be raw text.

Using methods like DoCmd.TransferSpreadsheet, although slick, do not always guess well as to field data types.

Mike

 

[Cbrine]

mdmilner,
I usually import the excel data into a raw data Table. Then using a append query, I assign the values to a table already created with the correct data types. If something doesn't match, you will get an immediate error. I don't see CSV or text as being an improvement over that. I would most likely need to do something similiar, to the CSV/txt file, if I needed to control the assigned data types. Of course, at the end of the day it doesn't really matter. The only problem I could see, is if you have other users dealing with the CSV files. It takes a certian level of understand to work with CSV files, versus a standard office tool like excel.

Cal

 

[Norie]

I usually import the excel data into a raw data Table

Cal

Do you mean a table that has just text fields?

 

[Cbrine]

Norie,
I just let it default to the basic import values, and if I need to reassign the data types, I do via an append query. That way, if any of the data needs to be translated into other formats, you can build a custom query field's. Sometimes, the dates, aren't quite dates, and times aren't quite times, so this give's you more flexibility. Of course the same would apply to CSV and TXT files as well.

 

[Norie]

Cal

So you mean that you let Access decide the data types?

I find that sometimes that can be a disaster. Recently I was importing data (about 100000 records) from 3 different files.

As far as I was aware the date field in each file was formatted the same - but it wasn't.

The import was done using code and I must of done it about 4-5 times till I realised what was wrong.

So I ended up importing in to a table with the date field as text type.

The imported table was appended to another table where the date field was of date type and all the dates converted fine.

 

[Cbrine]

Norie,
That situation seems really familiar. It's been a while since this last time I had to setup something like this, so perhaps I'm misremebering, cause it also sounds more logical. I think I may have bypassed that problem by not importing the titles(because some were invalid), even though the sheet had titles. So the first line of the table would automatically be ID'ed as a text field. Then I would filter the titles out in the query, as well as reformatting the date, and such.

Cal

 

[mdmilner]

That's exactly my experience. Frequently importing using the Access Guess Wizard is catastrophic and simply won't work. What I prefer is to import a raw text file using a specification and forcing potential trouble fields in as text. I then append it to a correctly formatted table that actually gets used.

I've actually gone so far as to 'package' this. Set up a few tables/forms/code to 'script' a series of actions. My current package will FTP/Import/Build(append)/Update(fix fields like dates) - I've also got one piece setup so I can build joined queries from my 'scripting' package.

Pretty much, I do imports once manually, then set it up to do it via code from that point on. Here's what my scripting table looks like - walk thru it sequentially to see what order things run.

Book1
	A	B	C	D	E	F	G	H
1	Desc_task	task_name	task_seq	task_type	srcFile	dstFile	optInfo	disable
2	CSXIPaidClaims	FTP_clmpd	12	OPENTBL	tblImod			FALSE
3	CSXIPaidClaims	FTP_clmpd	13	FINDFILE	S:\Intermodal\IntermodalClaims\			FALSE
4	CSXIPaidClaims	FTP_clmpd	1	FTP	FLRSFCP.FOC.ACROSSF.DATA	Across		FALSE
5	CSXIPaidClaims	FTP_clmpd	2	FTP	FLRSFCP.FOC.FCD1F.DATA	fcd1		FALSE
6	CSXIPaidClaims	FTP_clmpd	3	FTP	FLRSFCP.FOC.OFFCOREF.DATA	offcore		FALSE
7	CSXIPaidClaims	FTP_clmpd	4	IMPORT	Across	Across	acrossimport	FALSE
8	CSXIPaidClaims	FTP_clmpd	5	IMPORT	fcd1	fcd1	fcd1import	FALSE
9	CSXIPaidClaims	FTP_clmpd	6	IMPORT	offcore	offcore	offcoreimport	FALSE
10	CSXIPaidClaims	FTP_clmpd	7	BUILD	across	tblImod		FALSE
11	CSXIPaidClaims	FTP_clmpd	8	BUILD	fcd1	tblImod		FALSE
12	CSXIPaidClaims	FTP_clmpd	9	BUILD	offcore	tblImod		FALSE
13	CSXIPaidClaims	FTP_clmpd	10	UPDATE	tblImod		14	FALSE
14	CSXIPaidClaims	FTP_clmpd	11	UPDATE	tblImod		15	FALSE
Sheet1

Mike