I want to import a text file that is one continuous string at present when i try to import the file it treats the data as one row.
I would like to split so that a new row is created wherever an apostrophe
appears in the string IE (') I can use this as the delimiter when importing as one row, but for some reason it creates errors after field 30
I've included a sample of the data
Cheers
I have a list of 30 files I need to import into my access database. The files in the folder are named
1. Txt
2. Txt
3. Txt
4. Txt
5. Txt
6. Txt
7. Txt
8. Txt
When I did the first import I did it by doing an import text file, located the file 1.txt. The Import Text Wizard opened and I picked Fixed With so I could break out the information myself.After dividing that information I hit advanced and changed the field name to match what they should be. Next, then it ask me to save Import Steps and I said yes.
First: how I can use the saved import to import this file again using the text wizard
Second: how can I set it so it imports a numbers of files, maybe one or maybe all 30?
Third: how difficult would it be to all the file name in the first or last column?
I have a database that a user wanted created. They gave me code used in another database to import the text file automatically using code. I looked at the code but I don't see where the file is coming from. I am not familiar with the transfertext code. I need to import a text file into an access table. The text file needs cleaning up because it comes from another program and it has breaks and the header repeats in the middle of the file several different times. I need code to clean up before it is imported into the table. I can't see how this code works enough to use it:
Code:
Private Sub cmdImport_Click()
Dim mResponse As String
Dim mDir As String
Dim intRecordLength As Integer
Dim strRecord As String
Dim strRecordType As String
Dim strSQL As String
Dim strID As String
Dim strDate As String
Dim strLev As String
Dim strCode As String
Dim strBranch As String
'************** Check for populated stat table
If DCount("[ID]", "tblStats") > 0 Then
MsgBox "Clear previous data before importing a new file.", vbOKOnly, "Clear Data"
Exit Sub
End If
'************** End Check
'************** Get File
mResponse = GetOpenFile_CLT(mDir, "Select file to be imported.")
mResponse = LCase$(mResponse)
If mResponse = "" Then
'If no path specified, abort
MsgBox "No file selected, import cancelled."
Exit Sub
End If
mDir = mResponse
Do While Right$(mDir, 1) <> ""
mDir = Left$(mDir, Len(mDir) - 1)
Loop
'************* End Get File
'************* Set Label/Form
Me.lblProcess.Caption = "Importing " & mResponse
DoCmd.Hourglass False
DoCmd.RepaintObject acForm, "frmImport"
'************* End Label/Form
'************* Start Import
Open mResponse For Input As 1
Do While Not EOF(1)
intRecordLength = 28
Input #1, strRecord
If Len(strRecord) < intRecordLength Then
GoTo sLoop
End If
'If length is 41
If Len(strRecord) > intRecordLength Then
strID = Trim(Left(strRecord, 12))
strDate = Trim(Mid(strRecord, 14, 11))
strLev = Trim(Mid(strRecord, 26, 3))
strCode = Trim(Mid(strRecord, 33, 2))
strBranch = Trim(Mid(strRecord, 40, 2))
End If
'If length is 28
If Len(strRecord) = intRecordLength Then
strDate = Left(strRecord, 11)
strCode = Mid(strRecord, 20, 2)
strBranch = Mid(strRecord, 27, 2)
End If
'Insert into table
strSQL = "INSERT INTO tblStats VALUES ('" & strID & "','" & strDate & "','" & _
strLev & "','" & strCode & "','" & strBranch & "');"
CurrentDb.Execute (strSQL)
sLoop:
Loop
Close 1
'********** End Import
sExit:
'************* Set Label/Form
Me.lblProcess.Caption = "Import Complete " & mResponse
DoCmd.Hourglass False
DoCmd.RepaintObject acForm, "frmImport"
'************* End Label/Form
End Sub
Please help
Thanks
Hi!
I'm trying to import csv files into an Access 97 database, but the filename is different each time otherwise I would just use a commandbutton with the transfertext macro.
I've been searching for help to browse to a file and keep coming back to the same few pages but not being a programmer I can't figure out how to use the code.
Can anyone help me with simple instructions on how to do this?
Thanks.
Anna.:confused:
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.
hi guys,
i am importing a tab seperated file into a table, and one of the fields is a user comment.
all is well -- until a record is imported that contains comment with a carriage return followed by some more text. the text after the carriage return is imported as the next record.
if the file is tab seperated, why is the carriage return causing the import to act in this way?
thanks
rik.
I have a .csv (MS Excel) file I need to import, but one of the fields contains rich text format text.
Is there anyway to clean this up before or after it is imported?
Thanks,
T.J.
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'
All,
I have a database that I import text files to create tables. I ve been doing this awhile but now I have to pass it on to someone else to do and are trying to find a better way. this is what I've been doing: I import the text file into a new table. Then I copy and paste the data to the corresponding table. I know I can do an append query to do this but its about 12 text files I have to import into 12 different tables and I didn't want to take the time. I then run a query to combine and sum data from two different tables. The problem is that I have to copy and paste because if I import the text files directly into the corresponding table, the query don't run. I've tried change the field data type in the import wizard but it still doesn't work. My table datatype fields are: field1-text field2-number and field3-number. There is not field for number in the wizard but I use double as a field size in the design table so I select this in the wizard. I don't know how to resolve this other than spending time to create an append query or cut and paste. Please help
Thanks
I have source data that is tab delimited, but the data is interspersed.
<tab>T1F1<tab>T1F2<tab><tab>T1F3<eoln>
T2F1<tab>T2F2<tab>T2F3<tab>T2F4<tab>T2F5<tab>T2F6...<eoln>
T2F1<tab>T2F2<tab>T2F3<tab>T2F4<tab>T2F5<tab>T2F6...<eoln>
<tab>T1F1<tab>T1F2<tab><tab>T1F3<eoln>
T2F1<tab>T2F2<tab>T2F3<tab>T2F4<tab>T2F5<tab>T2F6...<eoln>
That sort of gives a feel for how the data is laid out. T1F1, means the data here goes into the first field of Table1, T2F1 means the data goes in the first field of Table2.
You can tell the difference etween table1 and table2 data because table1 data starts with a tab, and table2 data does not.
Can Access directly import this data?
I haven't found anything that would work, but hey, I am known to overlook the obvious solution, so I hope someone can help.
Thanks,
David
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
Dear All,
I need some help. I am writing a database for my new and small company. I receive internet orders from my ISP in an email. I want to find a simple way of automatically importing these emails into my "Customer Information" table.
The issue is that I have no control on the format of the email I receive from my ISP. The data is fixed width and stores the information horizontally rather than in columns. For example:
Quantity : 1
Price : GBP 199.00
Delivery : GBP 5.00
Total : GBP 204.00
I have been exploring the GetData option and using the advance settings to align fields. It seems (and I am not an expert user of Access) that the GetData function relies on the import source to be aligned in vertical columns rather than horizontally. This makes mapping the fields almost impossible.
Does anyone have a clever work-around? Either in Access or manipulating the data before it goes into the database?
Many thanks, Matthew (matthew@byatt.com)
I am trying to import a field into a data, which is either a number, a number and text or just text. Within Excel the format has been set to text. The data type for the field in Access has been set to text and yet for those records which are only numbers when imported the record is shown as a number in scientific format. Does anyone have any ideas how I can get the number to be a number in its full format?
View 2 Replies View RelatedI have a large Microsoft Word document that has a table. My delema is, I want to bring each row of the Word table into records in Access table. The problem is the way the text is formated. This is an example of how each table entry looks like in word:
__________________________________________________ _____________________________
Dogs come when they're called; cats take a message and get back to you.
- - - Mary Bly
__________________________________________________ ______________________________
A cat isn't fussy - just so long as you remember he likes his milk in the shallow, rose-patterned saucer and his fish on the blue plate. From which he will take it, and eat it off the floor.
- - - Arthur Bridges
__________________________________________________ _______________________________
Do you have any idea how I can import this text formated as such into Access?
Thank you in advance - John
I imported a table into Access from a SAP generated text file. The integrity of the data is fine after import. I then created a new table from the original that has the exact same amount of records and has the same unique field, which is MATERIAL. The problem I have is when I join the two tables in a query, Access returns approx 140,000 records. I am doing a regular join, what could be the problem? Is it possible the table data types are not formated correctly, b.c I imported them all as text so Access would import all lines.
I look forward to anyone's response.
TIA
Ease
I am trying to import several txt from a directory.
The following code is on a button in a form:
Private Sub ImportData_Click()
Dim myfile
Dim mypath
mypath = "G:FinanceAccountingRoyalty2006exports3rd QTR 06JUL 06 est"
Do
myfile = Dir(mypath & "*.txt")
DoCmd.TransferText acImportFixed, "import_data", "tbl_import_tables", mypath & myfile, False, ""
myfile = Dir
Loop Until myfile = ""
End Sub
The problem is this causes a infinite loop
Can anyone help?? Thanks
PS This code was from previous posting, where the transfer was excel files.
I am having a problem with importing text from one form to another one. I am logging into the company server and getting the database from there. But for some reason the text will not import on my machine but it will on others. Any help would be greatly appreciated.
View 1 Replies View RelatedGood morning. I am new to coding VBA and need some help if possible. I need to import a Comma Delimited Text file into a MS Access table. The records vary in length and may take up multiple lines of text before the next record. The first field contains the type of record and are all prefixed 1###, with the ### being variable. (1001 - 1100). If the record goes over one line the 2nd line with start with 1000. Here is an example: The records with 1001 continue to the next line with 1000 so you know it continues. The other records are all single lines starting with 1100, 1003, 1004, 1006...1017.
1001,00000000,00000000,00000000,00108888,00537906
1000,00100,"CAJUN MEAT ","CHUB "-0020380
1001,00000000,00000000,00000000,00020492,00130534
1000,00108,"RED BEANS ","CHUB ",-0221510
1001,00000000,00000000,00000000,00222572,00796807
1100,0000000000,0009605419
1003,01,000101,263558519,"QQ380427",5,08,19,107,16,07,58
1003,02,000202,263774367,"QQ380428",4,08,19,107,22,22,12
1004,1," "
1004,2," "
1004,3," "
1004,4," "
1006,00000000,00000000,00000000,00000000
1007,"$ OFF ",0000000000,0000000000,0000000000
1007,"FREE ",0000000000,0000000000,0000000000
1007,"SENIOR 10% ",0000000000,0000000000,0000000000
1007,"50% Police ",0000000000,0000000100,0000000001
1007,"CREW 50% ",0000000000,0000000310,0000000003
1007,"CREW 100% ",0000000000,0000001976,0000000009
1007,"MANAGER ",0000000000,0000000260,0000000003
1007,"BONUS ITEMS ",0000000000,0000000000,0000000000
1008,00107,00001,00065,06074,00347,00290
1009,01,"BREAKFAST SALES ",0000114476,02940,000091
1009,02," SALAD SALES ",0000009093,00233,000007
1009,03,"DRIVE-THRU SALES ",0000142716,03666,000106
1010,0000002646,0000009093,0000000000
1011,000,0000000000,003,0000005208
1015,"NET SALES ","+",0000389258
1015,"TAX ","+",0000023434
1015,"GROSS SALES ","=",0000412692
1015,"RESTAURANT BANK ","+",0000000000
1015,"ADJUSTED GROSS CASH ","=",0000412692
1015,"SEPLINE----------------------"," ",0000000000
1015,"AMOUNT DEPOSITED ","+",0000365609
1015,"GIFT REDEEMED ","+",0000000000
1015,"CREDIT CARD ","+",0000044844
1015,"MISC. INCOME ","-",0000000000
1015,"PETTY CASH ","+",0000000000
1015,"NET SALES ","-",0000389258
1015,"TAX ","-",0000023434
1015,"CASH OVER/SHORT ","=",-000002239
1015,"CASHIER OVER/SHORT ","-",-000001839
1015,"OVER/SHORT RECONCILIATION ","=",-000000400
1015,"SEPLINE----------------------"," ",0000000000
1015,"NET SALES ","+",0000389258
1015,"GIFT CERT. SOLD ","-",0000000000
1015,"OTHER NON-FOOD ","-",0000000000
1015,"NET FOOD SALES ","=",0000389258
1016,1,00000000,00000000,00000000
1016,2,00006103,00036415,00118800
1017,0004339338
I will need to do this weekly for many different files. I appreciate all the help that anyone can provide.
creating a import form into access.File format is txt (tab delimited)
Ideally I need:
- a box to enter the file name
- a box to enter the path
- a import button to push
I have seem some comments saying you have to creat a transfer text.However I have no knowlegde of Coding, opening or closing statments..
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?
I would like to create a table consisting of text passages. But i have a problem with it, explained in the following:
I just inserted a "newline".
And once again!
The problem is... when importing data, rows are created by splitting my textfile at the newlines.
But of course i dont want this. I want that the split occurs at controlled splitpoints.
Im trying to work with access 97, to import a text file into access. All the books i have looked in and web pages found say, file - get external data - import, then in "files of type" drop down you can select text.
I dont have the option to do this.
I have Uninstalled office 97, reinstalled it. with all the extras.
I have gone into the help and added all the valupack but still nothing.
Thanks :confused:
I used the get external data tab and went thru the process. everything looked good in the preview but when I clicked finish I got type mismatch errors and the data that was supposed to be in field 1 was in fieild 2 and so on.
View 1 Replies View RelatedI've had a look through the many topics on text importing, but can't find anything specific to my problem.
One of our suppliers has started offering their catalogue as a CSV file via e-mail. For now, I am saving the file to my computer, and wanting to import it into a database.
the table is a bit awkward, as it has "useless" data in the first field. Here is the beginning of one as an example:
VIP Computer Centre Ltd. Trade Price List. 22 August 2006 3:37 PM
ORDER CODE,PROD GROUP,DESCRIPTION,WTY,BOXED IN,1 OFF,5 OFF,20 OFF,UNIT
7719-C,BAREBONE SYSTEMS,JW MINIQ 430AV INSTANT-ON B/B,0,1,50,49.75,49.25,EACH
I imported the data via the Get External Data feature into a new table. This worked fine. I then changed a couple of values, and re-imported the data, this time selecting to import it to the new table. It came up with an error, saying 295 records were lost due to key violations. I then realised that this only appended the data on the end of the table, which isn't what I want.
Here is what I am trying to accomplish:
The field names are created from the titles in row 2 of the CSV file
The table is updated by importing the latest CSV file, so that any price alterations are changed, and any new products added (but identical data is ignored). If one field is needed as a constant, this would be the ORDER CODE field
Field 2 (PROD GROUP) becomes a combo list box (probably based on another table - so the table contains the categories, e.g. BAREBONE SYSTEMS, and the text from the CSV file is converted to the appropriate ID number)
Is any of this possible? If so, how?
Thanks in advance for any advice you can give!
Hello - I want to import a text file into a table in a database. The text file looks like this:
00001 06006025 1420 0010 Health Insurance 7
00001 06006025 1425 0010 Life Insurance 7
00001 06006025 1430 0010 Disability 7
00001 06006025 1440 0010 Profit Sharing 7
There is no consistent delimiter. The text field is making things complicated because there is no accurate way to separate the text field from the last number in each line ('7') which needs to be stored in a different column.
Is there a way to insert quotations arond the text string so the quotation mark can be used to distinguish it when importing into excel or a database table. This may work because the starting position of the text string is constant. The ending position would have to be defined as the place where the number appears ('7' in this case). Then, quotations need to be placed around the text string.
Is this possible? Any advice will help tons!!!
Hi,
I have a text file with comma separated values. What I want is a single access query to import this text file into an access table. I know this can be done through import text wizard in access - but what I need is a single query.
I have done the same thing for transferring oracle data to an access table - but for text file to access, I am getting -7778 error.
This is the query I have written
SELECT * into MY_ACCESS_TBL from [odbc;Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=MY_TEXT_FILE_PATH;].[MY_TEXT_FILE.txt];
Please help!!
Regards,
Suneesh