Convert File Before Importing

Mar 12, 2008

All, I have code to import multiple text files into multiple tables in access 2003 There are about 15 tables It works but I have two problems. 1st I was converting the files from another source to text and placing them into a folder where I created import specification files. The problem is I need to automate the conversion part. How do I use VBA code to look at the file and add the extension .txt so that the program can find the file in the code. Here's part of my code. I didn't list all 15


'Import Text files into corresponding tables

DoCmd.TransferText acImportFixed, "PhoneNoProblems Import Specification", _
"PhoneNoProblems", "X:DB_Working017_ErrorsTextFilesPhoneNoProblem s.txt", True

DoCmd.TransferText acImportFixed, "StrangeZipcodes Import Specification", _
"StrangeZipcodes", "X:DB_Working017_ErrorsTextFilesStrangeZipcode s.txt", True


In the textfiles folder, before the filename with .txt exist I have to add the .txt How do I add it automatically before the import?

2nd I need a cleanup routine to remove the additional lines in the text files like: list and sort which can appear randomily throughout the text files.
I hope I've explained this so you can help me
Thanks

View Replies


ADVERTISEMENT

Tables :: Importing Spreadsheet - How To Convert Data To PK / FK

Mar 14, 2013

I have a spreadsheet of generators with associated data: Make, Model, etc.

When I build the tables for the Make, each make will have it's own PK. Is there a good way to update the spreadsheet, replacing the Make for the PK other than using the find and replace function in excel (or update query in Access).

Example:

tblGeneratorMake
GeneratorID (PK)
GeneratorMake

John Deere has PK of 1
Kohler has PK of 2

If my spreadsheet has a list of Kohler generators, I need to change "Kohler" to "2".

View 14 Replies View Related

How To Convert .MDE File In To .MDB File In Access 97

Jul 18, 2007

Hi,

I want to make some changes in .MDB file and i do not have it. I have only .MDE file.. Could you please let me know how i can convert .MDE file into >MDB file so can change tabelsa nad reports...

Many Thanks

Nitesha

View 1 Replies View Related

Convert Access File To Exe File?

Dec 30, 2011

i want to convert accdb file to an exe file that not display any design view in other words i want the user watch only the forms and handle with the application as a program.

View 2 Replies View Related

File Browser (importing Txt File)

Nov 14, 2006

I want to put a File Browser on a form so my users can browse their desktop for the correct TXT file they want to be imported into the database. At first, I did nto think this would be hard but it seems as though it is some what of a challenge.

Any help is appreciated.

Thank You,
Marc

View 1 Replies View Related

Convert A Txt File To A Table

Jun 28, 2005

I have a txt file that includes X and Y variables like below, with a comma seperating the x from the y.

1.5,2
1.9,30.25
100.00,5.23
4.16,12.3
10.23,14.5
..
....
..... up to an unspecified number

how can I import the txt file data and place that data into a table.

A GPS device creates the txt file that will be placed in a folder.

thank you in advance

View 5 Replies View Related

Convert CSV File Received From UPS To Simpler Table

Jan 29, 2013

I am trying to convert a CSV file that I receive from UPS to a simpler table. I have over 3000 entries from 25 different locations and I need to evaluate the shipping cost. Is there a way in Access to convert the file as attached or should I be trying to use Excel?

View 2 Replies View Related

Importing .csv File

May 5, 2005

Hi all,
I am trying to upload a .csv file in a table using the tRansfertext method in vba. I have atext field having alphanumeric values. The upload is excluding the some values and generating a new error table. How do I get rid of this error.

Do I have to use a specification name. I have new .csv file to be uploaded every week, How do I work on the spec file ?

Any answers please..
Thanks

Nira.

View 8 Replies View Related

How To Convert .mde File To .mdb File?

Jul 18, 2007

hi,

i wnat to convert .mde file into .mdb file as i want to make some changes into tabels and reports..

please let me know how to do this.

Many Thanks.

Nitesha

View 1 Replies View Related

General :: Convert Web Access File To A Desktop Application?

Oct 21, 2013

How can I convert a web Access file to desktop aplication? I've created it from a model from Office.com, but I need create code in vba and it don't permit.

The use of file is only in desktop.

View 3 Replies View Related

Unable To Convert A Large File Into Ascending View?

Jul 2, 2014

The file was converted from excel. It is in Datasheet view. I select the first column and clip on the Ascending choice under the Home Tab. It works but leaves a large gab of blank rows. I go to the Database Tools tab and check Compact and Repair Database. The file returns to the original unorganized list.

View 1 Replies View Related

Importing Text File

Aug 7, 2006

We have received a zip file from a new client containing several thousand loans. The problem is that when I open the file as text all of the data is in one "field" meaning instead of going across the data goes down.
loan type,
loan number,
origination date,
loan term,
lo type,
etc....

Then it starts over with the next loan. There are over 17,000 lines.

How can I import the text file and have the loans go across. Even when I have imported the file using comma delimited it still imports them going down. Even if the comma delimited would work, I would still have issues because Access would not know when the new loan began to move it to the next row.

Any thoughts or suggestions? Sorry for my rambling.

View 6 Replies View Related

Importing Text File?

May 27, 2005

Hey,

I have a text file i wish to link up to one of my tables. The problem is that even if i try to import data from that file i get "Text file specifikation field separator matches decimal separator or text delimiter." message and after that one an err that the file cant be imported. Does anyone know why?

Thats the sample of my file:
'PV','045.302','KROG VEN S PNEU POG','JOHN-VALVE + AIR TORQUE','JV-9301NC-BW + AT051DA','','','','15','8','VANI','','1.4408','PTF E','ZRK'

View 4 Replies View Related

Need Help Importing Text File

Oct 26, 2006

Hi! There was a problem that one of the members had a while back, and the solution was never posted. Now, I am having the exact same problem. Here's the link.

http://forums.aspfree.com/microsoft-access-help-18/importing-multiple-text-files-125634.html

When I compile, it works ok, but then when I click my command button, it says I can't import this file. Any ideas?

KellyJo

View 1 Replies View Related

Importing Data From TXT File?

Aug 25, 2011

I receive a .txt file daily and want to import the information into a table. My problem is that the currency values in the .txt file do not contain a decimal. For instance $7.12 will come in showing as 712. How do I get Access to recognize it with the decimal?

View 2 Replies View Related

Importing XML File Into Access 2003

May 31, 2005

Hi, I have an xml which fields of text and some of numbers.

When I do: File --> Get External Data --> Import; I'm able to import the file.

The problem is that not all the data is entered in the fields. The colums containing the text are excluded.

While importing, I did choose the option "Structure and Data", so it should work.

Any suggestions on to why it is doing that?

Thanks.

View 2 Replies View Related

Importing Data From Excel Csv File

Aug 1, 2005

hi, i have struggled to import some data from an excel file into an sql database..
i have used phpmyadmin and certain code snippets but have failed miserably..

basically my excel file has lots of data, and within each cell, each bit of data is in single quotes...

eg.
'jonathan' '23' 'hardman' 'cheese'

there are no headings in the excel file (as in column or row titles) the data is just raw.
once i have made the table (with the appropriate fields and datatypes for the csv file) how can i import that data into a table using ms access???

View 2 Replies View Related

Dbf File Not Importing To Access 2003

Nov 3, 2005

I have a dbf file that I need to somehow get the table data out of, and into access, excel, or some other usable format. I searches the MS Knowledge Base and read several articles on updating the Jet 4.0 drivers (up to date) and updating Foxpro ODBC drivers, etc. I also searched these forums, and I have yet to find any solution that works.

I think this dbf file might be Foxpro, but I'm not sure. I don't know anything about Foxpro. I was getting an error message "unexpected error from external database driver (8961)", until I read an MS article stating that I should change the name of the Borland folder to BorlandOld and try re-importing. Now Access 2003 just says: "external table not in the specified format".

View 3 Replies View Related

Loss Of Primary Key When Importing Csv File

Apr 3, 2006

Loss of Primary Key when importing csv file

Hi All,

We are submitting an Access db table with ID as primary key to an outside company for processing. We export it as csv file and that's how it comes back.

After importing it back, we noticed that our IDs, which previously had 'gaps' as a result of deleted records, had been changed in to a new continuous sequence (Autonumbered). This, of course, completely destroys our database since related tables depend on IDs being unchangeable.

We ***suspect*** that the IDs are being destroyed during the process of re-importing the csv file. We are using the Access import wizzard.

Access msg: "Access recommends that you define a Primary Key for your new table". We then are given the following options:

1Let Access ADD primary key
2Choose my own primary key
3No primary key

During Option 1, the ID column is displayed and high-lighted. But we do NOT want that option since it offers to ADD a primary key, whereas we already have one (ID). At most, we want to 'declare' or 'define' a primary key, but we do not want Access to ADD one.

So we try Option 2, hoping that this will give us the chance to CHOOSE ID as our primary key. But the moment we select that option, the ID column disappears. We are not given the chance to select ID as our primary key.

So we try Option 3, hoping to re-instate ID as primary key as soon as we have an Access table again. But the moment we select this option, the ID column disappears.

-----------------------

If we GO THROUGH with Option 1, we notice that Access has renumbered our ID, the IDs of all our 'deleted' records have been re-assigned. Or so it appears.

If we GO THROUGH with Option 3, the resulting table does not contain an ID column at all.


Of course, the damage may have been done by the outside company which processed our file, but if so, we have to prove it, and we have to take measures to avoid it.

Any comments and advice?

Thanks.

Adrian

View 3 Replies View Related

Error Importing Excel File

Jul 6, 2005

So I have had Access for all of one day now. I am trying to import an excel file into access as a table. I successfully imported the first file but the second file gives me an error that says "An error occurred trying to import file ____. The file was not imported." What are the possible reasons this error has occurred? Both excel files I am importing are in the same format, so I am confused as to why one worked but the other did not. Thanks for any help.

View 2 Replies View Related

A Tough One - Importing A File Into A NEW Table

May 25, 2006

I work for a company that has a mainframe application that produces "outgoing" files. They are downloaded to a server and converted to ASCII (all at one time) and then ftp'ed to various clients. We are converting to a client server application and I am in charge of verifying that the mainframe application files (converted) are EXACTLY the same as the server application files. I came up with a process of loading the files to access databases and then with a series of queries and reports produce a list of exactly what doesn't match, which record, what positions and display the two fields that are mismatching.

My problem is that when I try to load the Mainframe file (note that it has already been converted and "eyeball to eyeball" the files are alike) using the "new" / import functions, Access rejects the file because it is greater than 65000 bytes. If I cut the file down to under 65k bytes it loads fine. I can load a 500,000 byte file that was created on the server in the first place.

Any suggestions? I can't change any of the processes that create the files. But I can change copies of the files so that I might be able to get them to load so I can verify that the data inside is the same. I'll let the geeks figure out how to fix the file/record control stuff.

(wouldn't have this problem if I was back on a mainframe!!!!)

Mac

View 3 Replies View Related

Modules & VBA :: Importing CSV File Into A Table

Feb 21, 2014

I am trying to import a csv file into a table... it works fine however it put all of the row in just one column ...

DoCmd.TransferText TransferType:=acImportDelim, TableName:="tblTempImport", _
Filename:=CurrentProject.Path & "/xxx.csv", HasFieldNames:=False

And i end up with tblTempImport only having one column F1....

View 14 Replies View Related

Tables :: Importing Text File

Oct 29, 2012

I am trying to set up an access button to go to a website, download its source code and import that txt file into a table so it can be parsed. Well, when I go to import this text file, it imports that data in a weird inconsistent order. The problem is I need the order to read exactly how it is from top to bottom, since the numbers I'm parsing from the code need to correspond to an XL Spreadsheet (which also gets imported).

I have the code set up, and everything is doing what it needs to do perfectly, EXCEPT this import. I know it's something stupid, but I just don't know what! I have everything being imported into a text file with an arbitrary delimiter that doesn't appear anywhere in the source code. Is there a way I pull this code in line by line in order into one field with X amount of rows so I can just run queries to pull the numbers I need?

View 13 Replies View Related

General :: Importing CSV File With Header Row?

Oct 3, 2012

Is there a way to automatically skip row 1 of a CSV file when importing? Row 1 contains a header with filename, date created, period covered, total record count, etc., and then Row 2 contains the column names.

View 5 Replies View Related

Importing Excel File - Characters Like < > Are Left Out

Mar 8, 2006

I have a problem whereby I want to import an Excel file into my Access db but certain characters like >< it doesn't accept. what other options do I have?

View 1 Replies View Related

Importing Data From Download Excel File

Feb 10, 2005

Hi

My question is: how do I set up the table to minimize redundant data. I have several fields that match the column headings in Excel, so the data can be imported, but fields like user name, pick slot, batch # all get redundant every time I import. My file is getting unnecessarily large. I know splitting the one table into many is the right thing to do, but don't have a clue how to import the data than. any help

Thanks

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved