visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: General :: Text File Importing SQL
By: srawtvl U.S.A.  Date: 04/10/2003 00:00:00  English  Points: 500 Status: Answered
Quality : Excellent
I need to import this kind of text file but very messy example below Any Help would be great

1CORP: 100 TESTTEST, INC ***** PROOF LIST ***** AS OF: 10/10/10 TEZP0320 PAGE: 1
SOURCE: 941 TEST TEST TEST RUN: 01/07/02 06:33:05 RCS : TE400

DEP ID: NDC0070411 SCAN: 3009 BANK: 000000 ACQR: 0047 CREATED: 1/07/02 04:11:12 VAL
BATCH-SEQ CARDHOLDER-NO TRAN DATE AUTHCD AMOUNT C/V REF-NUMBER POS CARD CT SL REPS TRAN ID VC ERROR DWGD I4
00000000000000000 TESt test test TN 37217 OUTLET: 00000 CERT: 3 K STR: 0000 TERM: 0047 CASH LET: 000

0839-0001 0000000000000000 41 0106 004131 0.00 00000000001 90 40 1263 3 Y 0000000000000 THQW
0839-0002 0000000000000000 41 0106 000311 0.00 00000000002 90 40 1263 3 Y 000000000000 TGV8
0839-0003 0000000000000000 41 0106 971572 0.00 0000000003 90 40 1263 3 Y 0000000000000NCB5


By: einstruzende Date: 04/10/2003 11:45:00 English  Type : Comment
At work I use perl scripts (and regular expressions) to parse the data out into a CSV file. If the results are small enough, I have the perl scripts insert the data into the database as well. Without knowing exactly what data you want out of there, I can offer no specific advise though.
By: VGR Date: 05/10/2003 20:08:00 English  Type : Comment
1 suppose the number of "header" lines is fixed and known to you. Let's suppose it's three (3)

Now, on "data" lines you've a fixed format, tanks god delimited by spaces (unless they are tabs)

then you've two solutions :

1) use a PHP "moulinette" to explode a line into column, then insert into DB
2) directly insert into DB

For (2), something like this would do :

LOAD DATA INFILE 'yourfile.txt' INTO yourtable FIELDS SEPARATED BY ' ' LINES TERMINATED BY '\n';
By: VGR Date: 05/10/2003 20:28:00 English  Type : Comment
live example... It's not perfect because your format is imperfect, but with some prealable data cleasning (replacing all multiple spaces with a single one, and checking the name the columns should have), you can reach your goal.

mysql> load data infile 'data.sql' into table importfixe fields terminated by ' ' lines terminated by "\r\n" ignore 7 lines;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 32

mysql> select * from importfixe;
+----+--------------+-----------+---------+--------+--------+---------+------+------+------+------+------+--------+---------+
| id | cardholderno | tran | thedate | authcd | amount | cvrefno | pos | card | ct | sl | reps | tranid | vcerror |
+----+--------------+-----------+---------+--------+--------+---------+------+------+------+------+------+--------+---------+
| 1 | | 0839-0001 | 0000 | | 0 | 41 | | 01 | 0041 | | | | |
| 2 | | 0839-0002 | 0000 | | 0 | 41 | | 01 | 0003 | | | | |
| 3 | | 0839-0003 | 0000 | | 0 | 41 | | 01 | 9715 | | | | |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+--------------+-----------+---------+--------+--------+---------+------+------+------+------+------+--------+---------+
4 rows in set (0.00 sec)

mysql>
By: VGR Date: 05/10/2003 20:29:00 English  Type : Answer
live example... It's not perfect because your format is imperfect, but with some prealable data cleasning (replacing all multiple spaces with a single one, and checking the name the columns should have), you can reach your goal.

mysql> load data infile 'data.sql' into table importfixe fields terminated by ' ' lines terminated by "\r\n" ignore 7 lines;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 32

mysql> select * from importfixe;
+----+--------------+-----------+---------+--------+--------+---------+------+------+------+------+------+--------+---------+
| id | cardholderno | tran | thedate | authcd | amount | cvrefno | pos | card | ct | sl | reps | tranid | vcerror |
+----+--------------+-----------+---------+--------+--------+---------+------+------+------+------+------+--------+---------+
| 1 | | 0839-0001 | 0000 | | 0 | 41 | | 01 | 0041 | | | | |
| 2 | | 0839-0002 | 0000 | | 0 | 41 | | 01 | 0003 | | | | |
| 3 | | 0839-0003 | 0000 | | 0 | 41 | | 01 | 9715 | | | | |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+--------------+-----------+---------+--------+--------+---------+------+------+------+------+------+--------+---------+
4 rows in set (0.00 sec)

mysql>
By: wenyonk Date: 06/10/2003 03:19:00 English  Type : Comment
I would first remove the following data:
1CORP: 100 TESTTEST, INC ***** PROOF LIST ***** AS OF: 10/10/10 TEZP0320 PAGE: 1
SOURCE: 941 TEST TEST TEST RUN: 01/07/02 06:33:05 RCS : TE400

Then import the data into ms excel. Create a macro to separate and prepare the data the way you need it.


I import data from oracle into Excel and then to Access on a daily basis, following is my process and code sample.

This is the segment code I use to import MS Excel Data from Orale to MS Access.

I use a Excel macro to prepare the data I need (proper GL account number). After the Macro prepares the data I use a dialog with the CommonDialog control on it to be able to pick the the excal file I want to import. I then click on an import button (see event below).

In the event code: I turn off system warnings, then delete the contents in a temp DB, import the Excel spreadsheet using the TransferSpreadsheet action. The TransferSpreadsheet action will import the the first spreadheet by default. The last parameter you can specify the sheet and range. After the temp DB is imported I clean the data once more and then run an append query to add the temp file to the production table. Turn back on system warnings. Close the Dialog and a Background forms.


Private Sub cmdImport_Click()

' Declare local variables.
Dim strImportFile As String

' Assign the value of the Import File Textbox to the local variable.
strImportFile = Nz(Me.txtImportFile, "")

' Turns the display of system messages off.
DoCmd.SetWarnings False

' Query that empties the "temp" import table (tblFleetAccountsPayableTEMP).
DoCmd.OpenQuery "qryFleetAccountsPayableTEMPDELETE"

' Use the TransferSpreadsheet action to import data between the current Microsoft Access
' database (.mdb) or Access project (.adp) and a spreadsheet file (.xls).
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblFleetAccountsPayableTEMP", strImportFile, True, "sheet1!B1" ' the True is "has field Names"

' Query that cleans up the imported data by removing all rows that contain "/" or "\" or "#"
' or "A" or "E" or "I" or "O" or "U" or "Y". Also removes rows where the Last 5 is greater
' or less than 5 digits from the tblFleetAccountsPayableTEMP table.
DoCmd.OpenQuery "qryFleetAccountsPayableTEMPCLEANUPDELETE"

' Move from "temp" table (tblFleetAccountsPayableTEMP) to final table (tblFleetAccountsPayable).
DoCmd.OpenQuery "qryFleetAccountsPayableAPPEND"

' Turns the display of system messages on.
DoCmd.SetWarnings True

MsgBox "The Import Fleet Accounts Payable Process is complete! " & vbCrLf & _
"Please verify that the Data in the Fleet Accounts Payable table is Correct. ", vbInformation, "Import Fleet AP Data"

' Close the dlgImportFleetAccountsPayableData dialog.
DoCmd.Close acForm, "dlgImportFleetAccountsPayableData"

' Closes the background panel if loaded.
If IsLoaded("frmBackground") Then
DoCmd.Close acForm, "frmBackground"
End If

End Sub


By: VGR Date: 06/10/2003 04:13:00 English  Type : Comment
not needed. Just replacing any two spaces contiguous with a single one (so that only one separates values), and using the IGNORE X LINES clause, is enough

you can't answer the Question by modifying all the data file... It would be too easy. Why not say "import as fixed format in eXcel - it's very good at this, that's true, except it will convert dates to DATEs, mangle the commas versus dots, align data on right with padding, etc - and then export as CSV ? Then the import in MySql would be straightforward.
By: wenyonk Date: 06/10/2003 07:43:00 English  Type : Comment
Coverting text to dates who cares you can change it to whatever you want to as part of your marco for preparing the the data

WenyonK
FL
By: VGR Date: 07/10/2003 04:14:00 English  Type : Comment
you love to complicate simple business :D

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 942.658900 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page