# Access: Importing as text **URL:** \ **Category:** Factual Questions\ **Created:** [December 19, 2008, 6:54pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953 "2008-12-19T18:54:18Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [December 19, 2008, 6:54pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953/1 "2008-12-19T18:54:18Z")
I use MS-Access to convert Excel files to fixed-position text files. When I import the files I have to go field by field defining them as text. I’ve tried selecting multiple fields, but Access won’t let me do that in the Import wizard. Is there a way to tell Access ‘When I import a file, define all fields as text regardless of what you think it should be’? ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [December 19, 2008, 8:40pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953/2 "2008-12-19T20:40:57Z")
I don’t know of a way offhand to make Access reformat Excel fields in the Access wizard. It usually takes the field type from Excel. Is there a reason you’re going through Access to produce a fixed-width text file, rather than exporting it directly from Excel? ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [December 19, 2008, 9:03pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953/3 "2008-12-19T21:03:18Z")
Excel has a 240-character record length limit when writing text files. Many of my files are longer than that. There is no such limit (that I’m aware of) saving as space-delimited text, but the text files I need to work with must have the data in columns and space-delimited will shift fields when data in them is longer or shorter. So the only way that I can get a text file with the data lined up in columns is to import it into Access and then export it as fixed-fields with column positions and lengths defined. ---
**Author:** ![NicorGasMan](https://avatars.discourse-cdn.com/v4/letter/n/c37758/32.png) [@NicorGasMan](https://boards.straightdope.com/u/NicorGasMan)\ **Post date:** [December 19, 2008, 9:18pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953/4 "2008-12-19T21:18:06Z")
If you are converting the same Excel files over and over, instead of creating new tables just have a table that is formatted the way you want it, and clear it before each import. Then append the records to that table when importing. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [December 19, 2008, 9:19pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953/5 "2008-12-19T21:19:31Z")
Sometimes the import wizard guesses the field type based on the content of the first few rows - if these happen to be numeric in some columns, it can make the wrong decision. Insert a dummy row at the top of the sheet, containing alphabetic characters, this should force the import wizard to assign all fields as text. Delete the record after importing. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [December 19, 2008, 9:41pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953/6 "2008-12-19T21:41:21Z")
> [@NicorGasMan](#): > > If you are converting the same Excel files over and over, instead of creating new tables just have a table that is formatted the way you want it, and clear it before each import. Then append the records to that table when importing. They’re different files every month. ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [December 19, 2008, 10:16pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953/7 "2008-12-19T22:16:00Z")
Aha. So you can’t save an import specification because your files change, which was my next suggestion. Do you have the Advanced button on your import wizard? I ask because my Access won’t allow me to change field types at all for Excel files in the wizard, but it sounds like yours does. I also don’t get the Advanced button for Excel files, but you may. If you have that option, you might look at it. I often use the Advanced setup because I find it easier to work in. All columns are listed with datatypes in a table setup, making it easier to just go down the list and edit as needed. Another option, as silly as this may sound, would be to export your Excel data to a delimited text format and then import to Access. I know you can get the Advanced options for text files, and I think that **Mangetout** ’s suggestion would automatically force them all to text. You could save a macro to your personal workbook in Excel to automate the export. In fact, you could probably add VBA code to the macro to add the header row too. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [December 19, 2008, 10:53pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953/8 "2008-12-19T22:53:53Z")
Alternatively, set up an Access table with more text fields than you would ever want, save it as ‘template’, then each time you want to do an import job, make a copy of the template table, import the data into it, then delete it when you’re done. If there are surplus fields, you could export from a query that ignores them ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [December 20, 2008, 1:33pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953/9 "2008-12-20T13:33:49Z")
Let’s distinguish between “different files every month”, and “different file _formats_ every month.” **Mangetout** has some great ideas assuming the format is consistent from month to month. OP: Is it? If not, are there just a few formats which occur over & over, or is each month’s data a totally new & exciting voyage of discovery? ---
**Author:** ![Stan\_Doubt](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@Stan\_Doubt](https://boards.straightdope.com/u/Stan_Doubt)\ **Post date:** [December 20, 2008, 7:01pm UTC](https://boards.straightdope.com/t/access-importing-as-text/477953/10 "2008-12-20T19:01:06Z")
Also, you mentioned that sometimes the text fields are greater than 240 characters. The limit for “text” data type in Access is 255 chars. If your data for these records happen to be greater than 240 and less than 256, you’re fine. Otherwise, there will be an error importing those records to “text” fields. Storing more than 255 chars requires a “memo” data type. I’ve used **Mangetout’s** dummy header row suggestion with success.