Nightmare- Importing probs...and more

[UCBFireCenter]

(Note: I've posted this with photos on the Microsoft Access livejournal...no help yet...hoping for some here)
Post Comment

So, I'm pretty much in a nightmare, that makes no sense to me why it is a nightmare.

I want to do one simple thing: Join the entire information of two tables based on a field of like values. I have 41 sets of two tables to do this too. With some of these tables I am having the same problems in importing.



I should add, all the sets of tables have gone through considerable formatting and data cleaning in excel 2007 to make the values alike. Originally, I was using the "VLOOKUP" function in excel to join these tables...however, the huge size of these tables (some have over a million rows) means it needs to be done in a much more large-database friendly access format.

I basically want what I would accomplish using "VLOOKUP" in MS Excel. "VLOOKUP" will perform a join of all the like values AND the inclusion of the data that did not have like values from the two sheets. It also creates a table for me...which somehow I'm not getting through MS Access Querying.

Here are the steps I've taken so far:

1) In Access 2007, I start by importing the two tables. I use External Data>Excel>Import, and select to create a table from this.
2) Enter Problem Uno One of my imported excel tables (notably the one with the most fields), has constant import errors. After Importing (and yes, I check that every field has the proper properties), I ALWAYS create a error table full of THOUSANDS of Type Conversion Errors. I read up carefully on them, but nothing I can find on the internet and in my texts answers my two critical questions about this...which are:

-DOES THIS CHANGE MY DATA IN ANYWAY (meaning the imported Access table is in any way different then my original Excel table)...and if not...

-WILL THIS AFFECT my join operation (notably the data types that are having Type Conversion Errors are not in the Field that I am basing my join on.

I should add that I've tried converting the excel (.xsls) to different file formats (like a .csv and .dbf) with the same Type Conversion Errors occuring.

3) So, skipping over the above concerns (which notably, mean I can't Join if I can't figure them out)....and moving onto Joining.

The closest operation I can find to get the results I want (and please, feel free to tell me if this isn't my best option) is creating a 1-to-1 relationship (i.e. Database Tools>Relationships) between the two like fields in the different tables, and then running a query (Create>Query Design).
Notably once I hit "Run" I get a "Join Properties" dialog box with 3 join options.

AS I understand it: None of these options (1,2, or 3) get me what I want: a table showing the joined values, and the un-joined values of both tables. For the time being I've been selecting option 1 (i.e. "Only include rows where the joined fields from both tables are equal")....but it's not really what I want. I know there is data in both tables that will not have corresponding data in the other table...and I'd still like to preserve that information if possible.

4) After running the Join (using Option 1...which isn't really what I want). I have a Query table. Fantastic (sort-of), but I want to make this a real table....so that I can add it into a master spreadsheet with it's 41 counterparts. After fishing around for awhile for a method to do this (apparently you cannot save a query as a table???) I chose to export this to an excel spreadsheet so that (hopefully) I could add it back into access again.

This was wildly to round-about to be logical...there has to be a better way (but I can't seem to find it).




HELPP!!!!!

[RuralGuy]

Let's start off slowly. You can append a query to a table. Does that help a bit?

[UCBFireCenter]

Hi Rural!

Not really, still stuck on major importing problems. Appending the query to a table is helpful though, if I get past my first problem (importing without the type conversion errors).

[RuralGuy]

Access make some assumptions on data types that my not be correct. If I were you, I would convert any troublesome Excel columns to text columns. They can easily be converted back to numbers once you have them imported.

[UCBFireCenter]

RG--

I've been getting the same error message when I'm saving from Excel into a text format. Error message is:

[this document] may contain features that are not compatible with Formatted Text (space deliminted). Do you want to keep the workbook in this format?

...it's a huge excel file. Is anything being lost though in the conversion?

[RuralGuy]

Probably not but it is hard to say from here. Is there any way to verify the data after you get it into Access?

[RuralGuy]

Have you tried saving your Excel worksheet and an .xls file?

[UCBFireCenter]

well, so I imported one of my problem tables as csv. The only way I can really verify if the excel-to-text conversion worked is see if all the rows are there (which they are 6962 in the excel sheet and the text sheet).

However, I'm having the same problem though in Access. I have generated a whole table of "import errors". They are all "Type Conversion" Errors, and there is over 1,951 of them...

[RuralGuy]

I too would be bothered by the import errors. You could Link to the Excel sheet as well as the import and we could probably come up with some code that would check each field in the table against the cell in the worksheet. It would be nicer if we could simply eliminate the import errors. How many cells horizontally are we dealing with? There is a 255 field limit on fields in a record. Are we up against that limit?

[UCBFireCenter]

Again RG--many thanks for looking into this.

I'm not entirely sure what you mean by the 255 field limit. Here are the stats of the set I've been working on today.

Before Join (Joining would = more columns) In one spreadsheet, I have about 45 columns and 6962 rows total (so that's 313290 cells total).

In the other spreadsheet I have 9 columns but (signifigantly) more rows 104,139 (so that's 937,251 cells total).

I should add this is not my biggest set of spreadsheets...

I've been looking at .xls ...is that the Excel 03 version of a spreadsheet? I'm working in 07 (which is part of the reason these spreadsheets are so huge, the '07 row cap is higher then the '03 cap). When I try to save as '03 I get an error about that.

[RuralGuy]

Fields in tables equate to Columns in spreadsheets. It does not sound like we are even close to any limits. Is there any way you could pare down a sample sheet to just a couple of rows and still get the import error? What sort of fields in the Spreadsheet are we dealing with? Any text cells with > 255 characters in them?

[UCBFireCenter]

RG-

Well, notably, the spreadsheet #2 (the one with 9 columns but significantly more rows 104,139--so that's 937,251 cells total). Just imported fine as a excel worksheet file.

Possible that the other spreadsheet (which is not importing well) has cells with >255 text characters. They are addresses and names. But, I kinda doubt it. The addresses are broken up into separate fields (i.e. one field for street, one field for city, one field for zip).

I highly highly doubt that it's >255. I'd even be willing to try to email you the data to show you the errors...frankly I can't tell what makes those 1,951 cells different then the rest.

I'm going to go home and try this on a '03 machine. BRB.

Thanks
Ari

[RuralGuy]

My email addy is Rural Guy at Wild Blue dot Net without spaces if you want to try that approach.

[RuralGuy]

For others reading this thread, we have been working off line and successfully imported the spreadsheets without errors by changing a lot of the Access assumptions to Text fields. I think we are good to go here.

[haze0025]

You guys are awesome. This thread was very helpful!!