Hello - I have recently begun a job where we need to transfer files to
Excel, make a few data manipulations, and then import the data into
access.

The problem is, the only way that I know to do it seems to take a lot
of time.

When I import the file to access, I have to go through each field to
identify it as text. Otherwise, I get import errors.

Is there a way to make this process more efficient.  For instance, can
I somehow let access know that ALL FIELDS are text, rather than having
to scan through each individual field to change them to text.

I tried saving the excel file as a text file, but access still reads
the numbers as "long integers" until I change them to text.

Thanks for any perspective you can provide me on this.

Any advice at all on importing files to access would be appreciated.

- M

Mike,
When importing from excel to access, access does a scan of the first 8 rows
of the spreadsheet.
If it finds any text in the first 8 rows of a column, it gives it a text
data type.
Access gives text a higher priority than numbers.
If you have long integers for the first 10 or so rows in a column followed 
by some text data, access will error out because it can't put text in a
number data type field.
If you have nulls for the first 10 or so rows in a column followed by
numbers, access will scan the nulls and interpret them as text data type and
then be unable to put the numbers in a text field.

Choosing the option of no column headings can make each column have one row 
of text data as the first row of each column.

Another option  might be to link to the spreadsheet and use append queries 
to move the data back into access.

If your excel data has mixed data types in a column, you could create an 
excel macro that converts all fields to text before you do the
import.

There are some options to change this default behavior by using ADO to do
the import (althought it is quite limited). I can't find the reference to 
this right now.

Others will probably add more suggestions to the list.

Jeanette Cunningham

"Mike C" <js2k111@yahoo.com> wrote in message
news:8e946219-d419-430e-8448-84299285e578@m36g2000hse.googlegroups.com...
> Hello - I have recently begun a job where we need to transfer files to
> Excel, make a few data manipulations, and then import the data into
> access.
>
> The problem is, the only way that I know to do it seems to take a lot
> of time.
>
> When I import the file to access, I have to go through each field to
> identify it as text. Otherwise, I get import errors.
>
> Is there a way to make this process more efficient.  For instance, can
> I somehow let access know that ALL FIELDS are text, rather than having
> to scan through each individual field to change them to text.
>
> I tried saving the excel file as a text file, but access still reads
> the numbers as "long integers" until I change them to text.
>
> Thanks for any perspective you can provide me on this.
>
> Any advice at all on importing files to access would be appreciated.
>
> - M

Oops,
this paragraph:
If you have nulls for the first 10 or so rows in a column followed by
numbers, access will scan the nulls and interpret them as text data type and
then be unable to put the numbers in a text field.

should be:
If you are importing into a field with number data type
and if you have nulls for the first 10 or so rows in a column followed by
numbers, access will scan the first 8 rows, scan the nulls and interpret 
them
as text data type and be unable to put text in a number field.

Jeanette Cunningham

"Jeanette Cunningham" <nnn@discussions.microsoft.com> wrote in message 
news:O6JoOVmhIHA.6136@TK2MSFTNGP03.phx.gbl...
> Mike,
> When importing from excel to access, access does a scan of the first 8 
> rows
> of the spreadsheet.
> If it finds any text in the first 8 rows of a column, it gives it a text
> data type.
> Access gives text a higher priority than numbers.
> If you have long integers for the first 10 or so rows in a column followed 
> by some text data, access will error out because it can't put text in a
> number data type field.
> If you have nulls for the first 10 or so rows in a column followed by
> numbers, access will scan the nulls and interpret them as text data type 
> and
> then be unable to put the numbers in a text field.
>
> Choosing the option of no column headings can make each column have one 
> row of text data as the first row of each column.
>
> Another option  might be to link to the spreadsheet and use append queries 
> to move the data back into access.
>
> If your excel data has mixed data types in a column, you could create an 
> excel macro that converts all fields to text before you do the
> import.
>
> There are some options to change this default behavior by using ADO to do
> the import (althought it is quite limited). I can't find the reference to 
> this right now.
>
> Others will probably add more suggestions to the list.
>
> Jeanette Cunningham
>
> "Mike C" <js2k111@yahoo.com> wrote in message
> news:8e946219-d419-430e-8448-84299285e578@m36g2000hse.googlegroups.com...
>> Hello - I have recently begun a job where we need to transfer files to
>> Excel, make a few data manipulations, and then import the data into
>> access.
>>
>> The problem is, the only way that I know to do it seems to take a lot
>> of time.
>>
>> When I import the file to access, I have to go through each field to
>> identify it as text. Otherwise, I get import errors.
>>
>> Is there a way to make this process more efficient.  For instance, can
>> I somehow let access know that ALL FIELDS are text, rather than having
>> to scan through each individual field to change them to text.
>>
>> I tried saving the excel file as a text file, but access still reads
>> the numbers as "long integers" until I change them to text.
>>
>> Thanks for any perspective you can provide me on this.
>>
>> Any advice at all on importing files to access would be appreciated.
>>
>> - M
>
>
> 

What format is the data originally in? If in Access, do all your 
manipulations withing Access and eliminate all of the transfers.

If they are text files, I would import directly into Access and again do the 
manipulations there.

If the above are not options, then I would suggest creating a table with all 
text fields and import your Excel data into that instead of into a new table 
each time. Of course, don't forget to empty the table prior to each import.
Once in your 'all text' table, you can then use append queries to move the 
data to whatever other tables you want.

-- 

AG
Email: discussATadhdataDOTcom
"Mike C" <js2k111@yahoo.com> wrote in message 
news:8e946219-d419-430e-8448-84299285e578@m36g2000hse.googlegroups.com...
> Hello - I have recently begun a job where we need to transfer files to
> Excel, make a few data manipulations, and then import the data into
> access.
>
> The problem is, the only way that I know to do it seems to take a lot
> of time.
>
> When I import the file to access, I have to go through each field to
> identify it as text. Otherwise, I get import errors.
>
> Is there a way to make this process more efficient.  For instance, can
> I somehow let access know that ALL FIELDS are text, rather than having
> to scan through each individual field to change them to text.
>
> I tried saving the excel file as a text file, but access still reads
> the numbers as "long integers" until I change them to text.
>
> Thanks for any perspective you can provide me on this.
>
> Any advice at all on importing files to access would be appreciated.
>
> - M 

This is a multi-part message in MIME format.

------=_NextPart_000_0138_01C886B4.4E1678F0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi Mike,

If this is a repetitive task with many sheets having the same format, =
and it sounds as if it is -=20

You might consider doing what I've done a few times - brute force at its =
finest.=20

If I have all text and Excel ->Access aren't playing nicely together (do =
they ever?) =20

I insert a new row 2 and clone something like bogus | bugus | bogus | =
bogus across this first data row.=20
Acess will see that text and assume, "Oh, this must be a text row."

You can use A | A |A to the same effect - if you have some short text =
fields to contend with.=20

Write a macro to insert the bogus row - and on the import side (Access) =
run a query to search and destroy the bogus row.=20

Hope this helps,
Gordon





"Mike C" <js2k111@yahoo.com> wrote in message =
news:8e946219-d419-430e-8448-84299285e578@m36g2000hse.googlegroups.com...=

> Hello - I have recently begun a job where we need to transfer files to
> Excel, make a few data manipulations, and then import the data into
> access.
>=20
> The problem is, the only way that I know to do it seems to take a lot
> of time.
>=20
> When I import the file to access, I have to go through each field to
> identify it as text. Otherwise, I get import errors.
>=20
> Is there a way to make this process more efficient.  For instance, can
> I somehow let access know that ALL FIELDS are text, rather than having
> to scan through each individual field to change them to text.
>=20
> I tried saving the excel file as a text file, but access still reads
> the numbers as "long integers" until I change them to text.
>=20
> Thanks for any perspective you can provide me on this.
>=20
> Any advice at all on importing files to access would be appreciated.
>=20
> - M
------=_NextPart_000_0138_01C886B4.4E1678F0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16609" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Hi Mike,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>If this is a repetitive task with many =
sheets=20
having the same format,&nbsp;and it sounds as if it is - </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>You might consider doing what I've done =
a few times=20
- brute force at its finest. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>If I have all text and Excel =
-&gt;Access aren't=20
playing nicely together (do they ever?)&nbsp; </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I&nbsp;insert a new row 2 and clone =
something like=20
bogus | bugus | bogus | bogus across this first data row. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Acess will see that text and assume, =
"Oh, this must=20
be a text row."</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>You can use A | A |A to the same effect =
- if you=20
have some short text fields to contend with. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Write a macro&nbsp;to insert the bogus =
row - and on=20
the import side (Access)&nbsp;run a query to search and destroy the =
bogus row.=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Hope this helps,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Gordon</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>"Mike C" &lt;</FONT><A=20
href=3D"mailto:js2k111@yahoo.com"><FONT face=3DArial=20
size=3D2>js2k111@yahoo.com</FONT></A><FONT face=3DArial size=3D2>&gt; =
wrote in message=20
</FONT><A=20
href=3D"news:8e946219-d419-430e-8448-84299285e578@m36g2000hse.googlegroup=
s.com"><FONT=20
face=3DArial=20
size=3D2>news:8e946219-d419-430e-8448-84299285e578@m36g2000hse.googlegrou=
ps.com</FONT></A><FONT=20
face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>&gt; =
Hello - I have=20
recently begun a job where we need to transfer files to<BR>&gt; Excel, =
make a=20
few data manipulations, and then import the data into<BR>&gt; =
access.<BR>&gt;=20
<BR>&gt; The problem is, the only way that I know to do it seems to take =
a=20
lot<BR>&gt; of time.<BR>&gt; <BR>&gt; When I import the file to access, =
I have=20
to go through each field to<BR>&gt; identify it as text. Otherwise, I =
get import=20
errors.<BR>&gt; <BR>&gt; Is there a way to make this process more=20
efficient.&nbsp; For instance, can<BR>&gt; I somehow let access know =
that ALL=20
FIELDS are text, rather than having<BR>&gt; to scan through each =
individual=20
field to change them to text.<BR>&gt; <BR>&gt; I tried saving the excel =
file as=20
a text file, but access still reads<BR>&gt; the numbers as "long =
integers" until=20
I change them to text.<BR>&gt; <BR>&gt; Thanks for any perspective you =
can=20
provide me on this.<BR>&gt; <BR>&gt; Any advice at all on importing =
files to=20
access would be appreciated.<BR>&gt; <BR>&gt; - M</FONT></BODY></HTML>

------=_NextPart_000_0138_01C886B4.4E1678F0--

Mike C <js2k111@yahoo.com> wrote:

>Is there a way to make this process more efficient.  For instance, can
>I somehow let access know that ALL FIELDS are text, rather than having
>to scan through each individual field to change them to text.

The quick and dirty way would be to create the second row as having
all text.  (Assuming the first row is column headings.)  Then ignore
it in your processing.

Tony
-- 
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

On Mar 16, 12:05=A0am, "Tony Toews [MVP]" <tto...@telusplanet.net>
wrote:
> Mike C <js2k...@yahoo.com> wrote:
> >Is there a way to make this process more efficient. =A0For instance, can
> >I somehow letaccessknow that ALL FIELDS are text, rather than having
> >to scan through each individual field to change them to text.
>
> The quick and dirty way would be to create the second row as having
> all text. =A0(Assuming the first row is column headings.) =A0Then ignore
> it in your processing.
>
> Tony
> --
> Tony Toews, MicrosoftAccessMVP
> =A0 =A0Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> =A0 =A0MicrosoftAccessLinks, Hints, Tips & Accounting Systems athttp://www=
..granite.ab.ca/accsmstr.htm
> =A0 =A0Tony's MicrosoftAccessBlog -http://msmvps.com/blogs/access/

Thanks to all for your replies!