Concatenate Text From Flat TXT Files

Jun 15, 2007

I receive reports formatted as a .txt (flat) file.
Problem: I have to develop a process to manage the .txt files so the data can be separated into its own fields. The layout of the output is as follows:

Date Time User Status Customer Name ID#

06/04/07 15:51 MRF C PTRSNEEO,TBYET L 01234567
Closed By:MRF acvd per xx adv xx of prices on her xxxx XYZASSX
TBLTSXS 75ZZ 90/09ds &5.35 XMEPRAZLE CCC 20XX
90/90ds &2.15 UTZXLPIES EX CCC 100XX 90/09ds &2.15
TYERLXP 0.125XX (TPSDTRM) 90/90ds &2.15 MNTYIMPS
90/90ds &2.15 XYZCNITSASLS TTT 20XX n/s gave # to
call back mrf

1) The layout of the flat .txt file does not allow it to be parsed vertically.
I.e. Vertically: Date and Time overlap the Secondary User information.
The User initials are not aligned. Customer Name is directly over the documentation text and of a different string length.

When I try to "cut" the data by the main sections into columns (Date / Time / User / Status / Customer Name / ID#) to import into Access the text in the documentation fields is "cut" into those sections as well.

I want to be able to reconstruct the text sections that are split apart into a single "cell" that can be exported to Excel or used within Access.

2) Also, some records have 1 line of text, others 2...up to 8. Each individual line of text ends up in its own cell. How can I "code" Access to read to the end of the last line of text (last cell) of the record and then concatenate the text in those cells into one continuous string of text in a single cell?

At the end of each record in the report there is a space separating the records.

I realize this is long and complicated, but am lost and the volume of this type of work is increasing. Requests to IS to change the output of the system reports is low priority so I am stuck. Thank you for any help you may provide.

View Replies


ADVERTISEMENT

How To Concatenate A Row In Query And Put In Text Box In A Form

Jan 1, 2013

How do I concatenate a row in a query and put in a text box in a form? I have a Query returning up to 10 dates the row is called [Date] I need to put them all together with a space and a comma between them and place that in a text box on a form.

View 2 Replies View Related

Queries :: Concatenate - Difference Between Numeric And Text Fields

Aug 6, 2015

I have a Query contains field that is :

Code : AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE SerName='" & [SerName] & "'" & " ORDER BY NormID")

but [SerName] is a text type field that is not Primary Key.

I have in the FamilyTbl , [ServiceID] is numeric type field, and a primary key. I try :

Code ; AllNv: Concatenate("SELECT NAll FROM NormalsQ WHERE ServiceID='" & [ServiceID] & "'" & " ORDER BY NormID")

but this returns all [NAll] records. I use concatenate :

Code:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant

[Code] .....

View 14 Replies View Related

Write A Concatenate Query And Display Result In A Text Box On A Form

Jul 20, 2005

Here's the form I'm trying to Create:

VEH POS NAME
A21: TC: CPT Somebody
G: SGT Someoneelse
D: PVT Noone

A22: TC: SFC Smith
G: SGT Jones
D: PVT Doe

and so on and so forth.

The VEH and POS are just going to be Labels in a form....no prob. Each Soldier's Squad and Team (for mounted Vehicle and Position) are stored in the Personnel Table. The below query is for vehicle A7 (ACTUAL would be the same as TC above). The query works. I just need to know how to get the result to display in a text box. What I'm planning on doing is creating text boxes for each posistion with these small select queries, so when I update the SQD and Team in the Personnel Table it updates on this form. Or is there an easier way to do this?

Dim strSQL as string
strSQL="SELECT [tblPERSONNEL]![RANK] & " " & [tblPERSONNEL]![LAST_NAME] AS NAME
FROM tblPERSONNEL
WHERE (((tblPERSONNEL.SQD)="A7") AND ((tblPERSONNEL.TEAM)="ACTUAL"))"

View 3 Replies View Related

General :: Concatenate Two Text Fields Into A Memo Field In Query?

Sep 5, 2014

I'm trying to concatenate two text fields into a memo field using an expression in a select query. My problem is that the text fields together end up more than 255 characters, so I need the resulting field to be a memo instead. I can't change the underlying text fields to memo fields because this is a large database used by others who need those fields to be text.

View 3 Replies View Related

Text Files

Sep 23, 2005

I have I table which is linked to a tex file. I created a code in which it will programatically refresh the linked, however, after the refreshed process is done, it always put the first record as a Field Header.

Can somebody help me what is needed inorder not to Access automatically use the first Record of the text file as a Field Header ?

Thanks :(

View 2 Replies View Related

Which View Do You Use - Flat Or Threaded?

May 16, 2005

How have you set up this forum? To view as a threaded view or as a flat list?

Poll is attached.

View 14 Replies View Related

Joining Flat Tables

May 7, 2014

simple join my Main Data Compiling Table and Secondary Table Delete from Main Data table (Located Under main Content Tables). Basically I want it to run like a flat data table which I cannot do because I have too many fields. I want ability to further expand it in the future.Somehow I need a one to one relationship with Referential Integrity enforced with cascade Update Related fields and Cascade Delete Related fields. Problem lies in it tries to create a one too many relationship.Both Main1ID and Main2ID are AutoNumber with Main One being primary key.

I have created a tabbed Navigation Form to show what is currently happening the first Tab is from Client Demographic Form generated by fields from Main Data Compiling Table and Second from Floral Information Form generated by fields from Secondary Table Delete From Main Data Table.

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

Appending Text Files

Oct 13, 2006

Hi.

Without importing into Access, how can I append 1 text file to a second text file?
I am using XP.

Russ

View 4 Replies View Related

Importing Text Files

Feb 7, 2008

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

View 1 Replies View Related

Databases And Text Files

Jun 6, 2005

I have an asp web page that allows a user to request information from an Access Database, the results of this request are then displayed on a second asp web page and finally the user can then select to view the individual results of the request on a third asp page.

For instance, a user can select all the registered names beginning with the letter 'A', the result of this request will be shown on page2.asp, the user can then select one of the names and the results of that request will be shown on page3.asp.

What I want to happen now is that on page3.asp I need to display some text, the amount of text is too long to put it directly in the Access Database, it needs to be in a separate file like a txt file or something. I need to know how to set up the field on the Access Database to call the text file and the asp code I need to use on page3.asp.

Any suggestions?

View 2 Replies View Related

Importing Flat Spreadsheet Into Normalized DB

Nov 29, 2004

So I’ve been assigned to develop a database in Access which will house information on students who are participating in an internship program sponsored by my employer. I was given a spreadsheet with roughly 40 columns, so it is pretty detailed info about the students. For example personal student info (DOB, email, phone, etc), as well as school, major, year rank, school address, permanent address, etc.

Importing this given table is easy, just by using the import wizard. However I am faced with a challenge. The info I have is up to date at this point however in years to come there will be a need to import new student info. The only way my employer wants that to be done is by taking the new excel spreadsheet and importing that data into the already created database. Now this wouldn't be a problem if the DB is one flat, non-normalized, table because the columns would line up. However I feel that the DB needs to be normalized because of the vast amount of data that is repetitive, such as State or Major.

Is there a way to import an excel sheet into an existing normalized access database? Or even a way to automate it so that the excel columns would be broken up into the separate corresponding normalized tables?

Thanks!

View 1 Replies View Related

Importing Fixed With Text Files

Jun 7, 2005

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)

View 4 Replies View Related

Importing SAP Text Files And Join Help

May 27, 2005

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

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

Tables :: Date In Text Files

May 1, 2014

I am importing a field from and old DOS based data base where dates and text are mixed in the same field. The field can have either one of more dates or text. That is the possible entries in the field are:

(1) 12/12/2013
(2) 12/12/2013, 12/1/2013 or some other delimiter other than a comma
(3) 6 CDs

When I tried to import the field, I am finding that (1) results in a string of numbers which represents the date. The others are, of course, OK.What I am trying to figure out is if the first type occurs and I have 41640 it will appear as 01/01/2014. So, can you have mixed field types in the same field by using some custom formatting with an if (of Iif) statement and what would that be? Something like IIF(IsDate([FieldA], format([FieldA], short date), @) I just cant seem to get the right nomenclature or number of correct arguments.

View 5 Replies View Related

Split A Query Into Two Text Files?

Jan 24, 2014

I have a very large query that I need to export to text. I'm hitting the 4GB limit, so I'd like to "split" the query and create two text files instead.

What's the most efficient way to do this? Also, are there any export file formats that can handle more data? (i.e. is there a format that could fit this massive query into a single file?)

View 10 Replies View Related

Online Entry To DB - Views On Flat File (CSV)

Feb 13, 2007

Hi Guys,

Does anyone have any experience of writing to an Access database via Web.

Do you think it is feasible, for an online entry form, to have 'customers' input their details or wahtever, then this info is sent to some sort of flat file?CSV is it called?

Then the admin can check these, then import to DB quite easily?

View 6 Replies View Related

Relational Structure To Flat Excel File?

Jul 17, 2007

Hope someone can help with this.

Basically we have an Access database with a main table, and several linked tables, joined on a unique ID.

For example :

tblFeatures :

FeatureID
FeatureName
etc

tblCategories
FeatureID
Category

So each feature can have many categories in a one-to-many relationship.

What I have a requirement to do is to pull that out as a flat file in Excel, ie

FeatureID, FeatureName, Category1, Category2
etc

What's the best way to go about this?

Cheers,
Iain

View 2 Replies View Related

Importing Text Files Option Missing

Mar 3, 2006

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:

View 2 Replies View Related

Printing Text Files From Access 2003

Mar 6, 2006

Can anyone help? I need to find a way to send text to my printer in Access 2003. All I can find is information regarding printing reports.

View 1 Replies View Related

Using Text Files Instaed Of Memo Fields

Mar 27, 2007

I have a large Db Which contains A Couple of Memo fields which due to the amount of data being stored in the Memo fields is starting to show signs of slowing down.One table contains 75,000, with all memo fields above 255.I wont have a problem with adding the extra code but was wondering if there is anything that may cause me a problem or if there's any reason(s) why I shouldn't follow this path.Many ThanksMickP.S. One Option would be to create 1-1 Tables and move the memo fields into the new tables that way I would still have an easy way of serching but it would reduce the size of the main table also none of the memo fields are included in any of the querys used, the problem with speed seems to be adding data I have checked the indexs which seem OK I can't remove any indexes Otherwise it slows down searching ACT.A copy Of the DB Is available here but it is an 11Mb Download should anybody wish to take a look at the tables structure and be able to advise on any imporvments that would be great please remember it's designed to store very large amounts of data.http://chartheaven.9.forumer.com/index.php?showtopic=107best wishesmick

View 3 Replies View Related

Importinf Text Data Files In MS Access

Jan 23, 2007

hi

i am importing the text format data files to MSAccess 2003 by using import option. but the problem is text data will change daily so i have to update the files daily. can we do this programatically.

Solution without useing DSN is prefered one if we don't hvae other option then i will go with that

Thank you

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

Queries :: How To Make Hierarchical Structure For Flat Table

Jun 23, 2013

I have 5 tables with below design

Table 1 : Docid (pk),DocNo

Table 2: Transid (pk),Transmittal No

Table 3: Docid (fk to table1),TransID(fk to table2)

Table 4: TransmittatoConID(PK), TransmittaltoCon

Table 5: Docid(fk to table 3),transID(FK to table3), TransmittatoConID(PK)
�combine fields�

I have made a query from top tables that returns the below data

DocNo TransmittalNo TransmittaltoCon

Doc-0001 tt-0002 con-0005

Doc-0005 tt-0002 con-0003

Doc-0001 tt-0002 con-0007

Now I like to make a query with adding new fields (parent) , (position) that shows

hierarchical structure of the fields

level 1 is (Doc No) level 2 is (Transmittal No) and level 3 is (TransmittaltoCon) ,

ID PARENT POSITION OPTION

1 0 0 DOC-0001 2 1 0 TT-0002 3 0 1 DOC-0005 4 1 1 CON-0007 5 3 0 TT-0002 6 5 0 CON-0003

View 1 Replies View Related







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