Importing Multiple Lines Of Text

Aug 27, 2007

Good 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.

View Replies


ADVERTISEMENT

How To Keep Multiple Lines When Importing Data From Excel To Access

Sep 7, 2011

I face a multiple line problem when i try to import data from Excel to Access db. Is it possible to keep multiple lines of a cell after transferring to Access. Since Access see any ceel of Excel as a Field, it takes all the lines as a line in the same row.

In enclosed, you can see a sample Access document. I put a point(.) between every lines in the same field manually. Is it possible to split up those data from these points and make a new line in the same field by VBA? For example,

BEFORE:

1) 232N1635-2.2) 412N1168-7

AFTER:

1) 232N1635-2.
2) 412N1168-7

View 1 Replies View Related

Multiple Lines In Text Box

Dec 14, 2004

i'm no noob, but i'm also not the expert, what i'm trying to do is this,
i'm taking multiple string out of an recordset and putting them in one big text box
but the problem is that after every record there has to be an enter in the text box
So for example:

record 1 =
-bla bla bla
-tja tja tja

record 2 =
-waa waa

my result in the text box must be:
-bla bla bla
-tja tja tja
-waa waa

and not (like it is now):
-bla bla bla
-tja tja tja -waa waa

who has a clue? chr(11) & chr(13) do not work, i activated "on enter go to next line"

thx in advance for helping out

View 2 Replies View Related

Import Text File / Multiple Lines And Varying Delimiters / Size To One Record

Aug 22, 2012

Using MS 2007, I have a 200 text files exported each day from another application that has two different types of lines (see below). I would like to import each text file in to a database as a single record.

Text file example (text.txt):

R111 WC 8/21/2012 7:00 Doe, John doej 10110110

First Question? Y
Second Question? N
Third Question? Y
...
Seventeenth Question? 10

As you see, I have the first row with multiple fields, but the next rows I have a question and an answer.

I would like to have this data imported as shown in the attachment. Example.zip

Most answers I see are for either multiple lines (same data and sizing). I am not sure how to handle several different lines with that vary in size and delimiters.

View 5 Replies View Related

Importing Multiple Text Files

Aug 1, 2006

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.

View 5 Replies View Related

Modules & VBA :: Importing Multiple Text Files?

Dec 17, 2013

Currently I have a code that imports a single text file into a table without problems.

I need to be able to selecte multiple text files and import them at once.

Here is my current working code.....

Code:
Private Sub Import_Click()
Dim strFilter As String
Dim strInputFileName As String
strFilter = ahtAddFilterItem(strFilter, "Text File (*.txt)", "*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
Filter:=strFilter, _
DialogTitle:="Please select new file...", _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
DoCmd.TransferText acImportDelim, "Import Specs", "Data Table", strSaveFileName, True, ""

View 14 Replies View Related

Importing Excel Sheet Into Access, Phantom Lines

Apr 25, 2007

Hi,

I have been importing excel spreadsheets succesfully for a long time now and this new problem I have never seen before. I don't know how to deal with it. I have had a look on here but could find nothing similar, I have not really had a any luck searching google, mainly because I am at wits end to find a suitable search term.

Here's what's happening:
When i upload the sheet as is, it works fine, bar a "data conversion error" for one column which contains mainly numbers but a few cells of letters.

as before, I have inserted a line at the top of the spreadsheet (below the column headings), and put a 1 in for each numeric column and a letter for each alphanumeric and text based column.

Now when i import to access, I get no conversion error, but access has inlcuded about 20'000 blank lines between the headings and the actual data.

I really don't know what is going on.

View 3 Replies View Related

Queries :: Multiple Lines In A Field?

Jan 8, 2014

I've exported some data which has 3-4 address line within the same field which is very odd.

The row has all the usual data, but when I look at the data in datasheet view the adresss line 1 shows only but when I click into the field and press down, more address info comes up. Is there any way to have this on one row within the field rather than multiple.

A bit like in Excel when you Alt+Tab in a cell it drops down but is the same cell, well this exactly like that in Access 2003.

View 1 Replies View Related

Split One Record Into Multiple Lines

Mar 10, 2013

I have an access form that is used for cash-ups. The cash-ups are not done on a regular basis and the owner requested that the database look at the last cash-up date, current cash-up date and calculate the difference in days and then calculate the average take per day by dividing the total take for the period by the number of days from the last cash-up date to the current cash-up date. This was simple and is done however, what he now wants is to export this data to another table and have it split the total amount per record by the average number of days and reflect the average amount per day over as many lines.

Eg: R5,000.00 / 20 days = R250.00 per day.

This data needs to be displayed in TWENTY lines each with a value of R250.00.

View 1 Replies View Related

Write/update Multiple Lines Belongs To A PO?

Apr 18, 2008

Hello all,

How are you? Hope everything is well!

I have a question. If I have a form, which has underlying source is the table LineItem. The table has these fields PO#, Line Item, Cancel check box, and Cancel Reason list box. Each PO can have 1 to multiple line items. Once in a while, the users need to cancel certain line items and type a reason in the Cancel Reason box. When the users click the Cancel check box, it force the user to enter a reason.

However, in cases, some POs have 10-100 line items, and the reason are the same for all line items, and it is time-consuming and tedious to do one by one. I would like to find a way to enter reason in line 1, for example, and copy/update the rest of line items in that same PO with that.

Please advise HOW and WHAT COMMAND should I use to update the text in one field of one record (line item) to ALL other records (line items) in the same PO.

Please help! Thanks so much.

View 3 Replies View Related

Queries :: Possible To Split One Table Into Multiple Lines?

Oct 27, 2014

My boss made a satisfaction database that tracks satisfaction in 2 ways, so we have:

Table

ID
SatisfactionAType
SatisfactionAScore
SatisfactionBType
SatisfactionBScore

I'd like to make a query that would split these into two lines, one for A, one for B and then export it to excel.

View 4 Replies View Related

Forms :: How To Prevent Multiple Lines In Field

Apr 13, 2015

Ive had a recurring problem with one of my databases

ive got a form with a subtable on it, the subtable is very important as much of the DB's data is entered in there.

every once and a while one of the users will be doing data entry and on a certain field (a short text field if that makes any difference) theyll push enter instead of tab, to move on to the next field, enter creates a new line instead of moving to the next field, when a new line is created the user will either assume the field is blank and retype the data or ignore it entirely and move on

the trouble is one of my queries completely fails to work when there are multiple lines in this field (comes with a data type mismatch error)

is there a way to prevent the field from being able to contain multiple lines (like a property setting or some VB code?)

View 3 Replies View Related

General :: Indenting Second And Subsequent Lines In Text Box

May 12, 2013

In a report, I have a text box with variable length text. Can Grow is set to Yes. If the box grows, I would like all the subsequent lines to be indented. Is that possible?

View 2 Replies View Related

Modules & VBA :: Count Number Of Lines In A Text Box

Oct 17, 2014

I have a memo field that feeds to a textbox control on one of my forms. I would like to re-format the textbox using the .Height value based on how many lines appear in it (i.e. it will fit only 3 lines if there are 3, but 5 lines if there are 5, etc.).

Is there any way to count the number of lines that appear in the textbox?

View 3 Replies View Related

Replace New Lines After Search String With Corresponding Text With A Tab

Mar 25, 2014

I need to loop through text files and replace the new lines following the from, to, copy and subject fields with tabs and place the associated text on the same line as the from, to, copy and subject fields.

I start out with this. See below.

From:

ABC COrp@abc.com
To:
XYZ Inc.@xyz.com
Copy:

Me@abc.com; Myself@abc.com; Irene@xyz.com

Subject:

NOthing much

I need it to be like this.

From:ABC COrp@abc.com

To:XYZ Inc.@xyz.com
Copy:Me@abc.com; Myself@abc.com; Irene@xyz.com
Subject:NOthing much

How can I do this in access?

View 14 Replies View Related

Help With Select Query To Get Single Line Instead Of Multiple Lines

Dec 21, 2007

Hi,

I have a query that selects values from a table and I'm further creating a letter based on this query. The problem i'm having is that if the query pulls 3 rows as result then it creates 3 separate letters. Basically this is how the output of query looks:

(refer attachment)

Is there any way to write this query so that I can get output as below:

Col1 Col2 Col3
10 20 10


Please advise.

View 7 Replies View Related

Multiple Lines In Order Form From Single Database

Feb 28, 2005

I have 2 databases Product, with 3 entries ProdID, ProdName and UnitCost.
2nd database Order has Order ID, ProdName, UnitCost, Quantity, SaleDate and Total.
I want to create a form to input data into the Order databse that allows me to enter several rows using the data from the product table by selecting ProdName(Product) from list and UnitCost(Product) being inserted into each row automatically as a new ProdName is selected.
Can anyone hsuggest how best to do this.

View 4 Replies View Related

Forms :: How To Break Update Statement Into Multiple Lines

Mar 7, 2014

I am unable to break the UPDATE statement into multiple lines and now I am getting "Syntax Error in UPDATE statement"

Following is the multiple line Update Statement

Dim strsql As String
strsql = "Update tblcurr" & _
"SET tblCurr.Currencyname =" & _
"[forms]![updatecurrency]![txtcurname]" & _
"WHERE (((tblCurr.Currencycode)=" & _
"[forms]![updatecurrency]![txtcurcode]));"
DoCmd.RunSQL strsql

Following is the one liner Update statement which, works perfectly

strsql = "UPDATE tblCurr SET tblCurr.Currencyname = [forms]![updatecurrency]![txtcurname] WHERE (((tblCurr.Currencycode)=[forms]![updatecurrency]![txtcurcode]));

View 4 Replies View Related

Forms :: Duplicate Lines In Multiple Form And Query

Jul 8, 2015

The purpose of my database is to keep a record of training activities undertaken by staff. There is therefore a multiple form which displays, for a specified worker, the list of all the required activities. Each line has a button which opens another form to allow an activity to be started (together with start date and other info). The main tables are Worker, Activities, and a junction table Worker2Activities. On closing the second form and refreshing the first one, the started activity is correctly recorded, but on as many lines as there are staff on the course. To make matters worse, these also appear when i then select another worker (and if i create another worker, the number of lines goes up by one for all of them). The Worker2Activities table correctly show one new record created

View 3 Replies View Related

General :: Saving Invoice With Multiple Lines In The Item Section

Jun 17, 2014

I want to make an invoice with products and services and totals, the thing that has me wondering is how do I save an invoice with multiple lines in the item section of the invoice. I want to be able to choose a customer and then see the list of invoices connected to that customer, and when I click the invoice to open it as it was. What would be the best way to do this?

The invoice contains:Customer info, adress, name...etc

List - multiple lines of products sold / services completed
Totals, date & time...etc
Relationship goes like this: Customer -> Invoice <- Inventory / Services

View 3 Replies View Related

IMPORTING MULTIPLE FIELDS FROM MULTIPLE DATA SETS INTO A TABLE

Nov 16, 2006

I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.


THE FIELDS REQUIRED IN THE SESSION TABLE ARE,
Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made

The CSV data set attached

tHANK YOU

SIMON

View 2 Replies View Related

Modules & VBA :: Importing Text Using Text Wizard

Jun 2, 2015

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?

View 2 Replies View Related

Importing Text

Feb 8, 2006

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

View 1 Replies View Related

Importing Text

Feb 22, 2008

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

View 5 Replies View Related

Importing Text Files

May 1, 2006

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:

View 5 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







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