Export to Excel setting field types

[cynthia]

I am exporting a query out to excel. The client then fills in the fields and
we import the data back into a temp table that we sort through before
actually populating the assorted tables. My problem is when I export to
excel all fields show as general in excel, then when I import back if the
first 50 or so fields are blank it sets the field to a number type and the
text fields do not import.

Any help would be greatly appreciated.

 

[Jeff Boyce]

Cynthia

So, is the problem with the users not being able to use Excel to enter their
data, or with your database not being able to interpret the Excel?

If the latter, a more common approach is to NOT expect the imported/linked
to Excel data to be particularly clean/suitable. If you assume that's
given, then just let the data be what it is, but use queries to "parse" that
raw import data into the data types and tables you need.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Gina Whipp]

cynthia,

Try setting up an Import Specification. Doing it that way you can set the
Data Type of your fields and it will no longer matter if there is data in
them or not.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[cynthia]

I do not know what you mean by Import Specification. The only way I know to
import from excel is to dump all the data into an existing table or let it
create one. I found that if I create the table first with all text fields I
have better luck. But that is causing other issues and I am still not
confidant I am getting all the data across.
I have stated below what I am trying to accomplish and what problems I am
having. Quite possibly I am going about this the wrong way.

1. I have a table with 70 fields i.e. (field1 is Tag /field2 is Length
/field3 is Status)
2. Depending on the client when I dump to excel field1, 2 and 3 etc need
the name changed to what the client wants, and when I import back the client
will have their field names and I will have to populate back to the field
name I have. Each client is different. The user selects which client before
I do the export/import.
3. I have a matrix set up so the users can add clients and set what the
client expects for each field name, and if left blank I will not export that
field. I intend to check the import against this matrix and stop the import
with an error listing field names not in the matrix.
4. I can create this temp table each time before the import (sounds like a
lot of coding with the matrix). If I create this table before the import
having a temp table for each client using the matrix (which the users can
modify) I need to write code to update field names or remove fields for a
client when the matrix is updated. I cannot find any way to remove a field
name from an existing table.
5. Is there a way to write code so it finds the excel field name and I can
change it before importing to the table?

 

[Gina Whipp]

Cynthia,

First, I did not know you were first dumping to a table then exporting or is
that only for importing? Your initial eMail indicated you were exporting a
query, so I am a little confused. What version of Access? Setting up an
Import Specification differs depending on versions and I think this may be
what you need.

Just wondering, did you consider Jeff's idea of linking?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[cynthia]

I am exporting using a query, but importing into a table so I can then run
queries to populate the tables and fields per our database. We have Excel
2007

I do not know how to link to the excel file. Or how I would query the data
once I did. Whatever I do it needs to be seamless, so the user doesn't have
to do anything except hit the import button in Access and select which client.

For some reason I no longer receive emails when anyone responds, I just keep
checking in ever so often. I don't want you to think I am not responding as
I should and wanted to let you know I really appreciate the help on this, I
am stumped on what the correct way to go. We will be importing a thousand
rows with 70 or so fields, so it is important I don't just slap something
together. Most of my tables are linked SQL tables, but I was planning on
making these temporary tables in Access only.

 

[Gina Whipp]

Cynthia,

No worries about response time... Take a look at
http://www.regina-whipp.com/index_files/ImportExport.htm

At the top on the Import section press the *More* button and follow the
steps... Then look up TransferSpreadsheet in Help and place the name of
your Import Specicification in the appropiate place. All this can be
attached to a button.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[cynthia]

Gina,

Thank you for the information, this was a function I was unaware of.

I am doing this right now using my query and the TransferSpreadsheet.

I do not see how this is going to help me in renaming the fields when I am
doing the import, and how this will help ensure I am not loosing data due to
conversion type failures.

The matrix form that the users input will let me know what fields from the
client excel file match to my table fields, but I will need to find the best
way to get the data from excel to a table without chancing loosing data.

I feel the best step would be to create the table myself before the import,
but I do not know who to remove or change fields in an existing table in code.

 

[cynthia]

How do I use queries to parse raw data coming from excel without placing it
in a temporary table first?

 

[Gina Whipp]

Cynthia,

Answers in-line...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,

Thank you for the information, this was a function I was unaware of.

I am doing this right now using my query and the TransferSpreadsheet.
***Great!

I do not see how this is going to help me in renaming the fields when I am
doing the import, and how this will help ensure I am not loosing data due
to
conversion type failures.

Once you have the TransferSpreadsheet done... which will place your data in
a table with the fields named the way you want them.


DoCmd.TrasnferSpreadsheert TextTransferType, "NameOfSpecification",
"NameOfYourTableWithCorrectedFieldNames",
"FileAndLocationOfFileToBeImportedIn, True/False HasFieldNames, ""

The matrix form that the users input will let me know what fields from the
client excel file match to my table fields, but I will need to find the
best
way to get the data from excel to a table without chancing loosing data.

See Above...

 

[cynthia]

I am feeling pretty dense right now.

On the DoCmd.TransferSpreadsheet TextTransferType, "Name of Specificaitoin",
"nameofYourTableWithCorrectedFieldNames",
"Fileloc of file to be imported in.

What do I fill in for texttransfertype? AI can find is acimport or acexport?
Are you telling me I can force it to transfer all as text or just that I
need to do acimport?

Name of specification if it is the ImportSpecification, I don't know how to
make this work since it will be different depending on what changes the user
makes to the matrix. Can this be substitued for a query?

 

[Gina Whipp]

Cynthia,

TransferType is asImport..

DoCmd.TransferSpreadsheet acImport, "ImportSpecification",
"NameOfYourTableWithCorrectedFieldNames", "Fileloc of file to be imported
in"

Now you just need to fill in the other fields. I don't know what you mean
by can it be the name of the query? You are grabbing your Excel Spreadsheet
and putting it in the table in the Access database. Once in your temp table
you can either write a query to append to another table OR place the data in
the table that is going to hold the data. Here's a web page that might
explain it better... http://msdn.microsoft.com/en-us/library/bb214134.aspx


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Jeff Boyce]

One way would be to 'link' to the Excel data rather than import it.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[cynthia]

Thank you for the help on this.
--
Cynthia

Cynthia,

TransferType is asImport..

DoCmd.TransferSpreadsheet acImport, "ImportSpecification",
"NameOfYourTableWithCorrectedFieldNames", "Fileloc of file to be imported
in"

Now you just need to fill in the other fields. I don't know what you mean
by can it be the name of the query? You are grabbing your Excel Spreadsheet
and putting it in the table in the Access database. Once in your temp table
you can either write a query to append to another table OR place the data in
the table that is going to hold the data. Here's a web page that might
explain it better... http://msdn.microsoft.com/en-us/library/bb214134.aspx


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

Are you saying you got it to work?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm