insert aata from Excel file into IMB Database

[access1denied]

is it possible to insert data from an excel file into a DB2 database (ODBC connection) table?

[DataMystic Support]

yes!

Just use Filters\Convert\Excel spreadsheets to text

Follow this with search/replace or add/remove columns to get just the data you want

Follow this with search/replace to convert CSV data into SQL (e.g. just Add a left margin

Code: Select all

  insert into tablename (col1,col2,...) values (

add right margin

Code: Select all

);

Then finally, add a Filters\Special\Database connection

[access1denied]

Sorry for the late response... I didn't realize I would have gotten an answer... I will try your suggestion immediately.

[access1denied]

The suggestion is not working for me...

My excel file contains eight columns. I have created the filter, but I doesn't appear to update the table. I was hoping to upload the filter, but I am getting a message that reads: The extension fll is not allowed.

My filter looks:
TextPipe Edition 9.9

Filter Title: S:\test\Excel2DB2.fll

Filter List
-----------
Filter options
| [ ] Log to file
| [X] Append to logfile
| Log filename: %USERPROFILE%\textpipe.log
| Threshold 500
|
|--Input from file(s)
| [ ] Confirm before processing each file
| [ ] Confirm before processing read/only files
| [ ] Delete input files after processing
| Skip binary files
| Sample size 100 characters
|
|--Convert Excel spreadsheets to text
|
|--Comment...
| Click >>Here<< or press F1 for help
|
| TextPipe uses 'filters' to perform changes to each file. This 'filter list' is a sequence of changes, like 3 different search and replace operations.
|
| You can add filters from the Filter menu above - Convert, Add, Remove, Unicode, Replace, Special, Maps, Email, Restrict.
|
| For:
| * Search/replace - look in the Replace Menu
| * Unix/DOS/Mainframe End-of-line conversion - look in the Convert Menu
| * Extract email addresses - look in the Email Menu
| * Remove HTML codes - look in the Remove Menu
| * Join (merge) files together - look in the Special Menu
| * Split files apart - look in the Special Menu
| * Join files and then sort them - add a Special Menu\Merge filter followed by a Special Menu\Sort filter
|
| Just click the menu item to add the filter to the list.
|
| You'll find many examples just by clicking File\Open, or you can use File\Search to locate a filter.
|
| You can also ask questions in the TextPipe Discussion Forums -
| http://www.datamystic.com/forums
|
|--Insert column 0 []
|
|--** DISABLED ** Replace [insert into tablename (col1,col2,...) values (] with []
| [ ] Match case
| [ ] Whole words only
| [ ] Case sensitive replace
| [ ] Prompt on replace
| [ ] Skip prompt if identical
| [ ] First only
| [ ] Extract matches
|
|--Add left margin [insert into scdata.mvminv (PLACEMENT,ACCOUNT,PLACEDDATE,PLACEDAMT,VENDOCODE,VENDORTYPE,BALANCE,PATTYPE)]
|
|--Add right margin [);]
|
|--Database Provider=IBMDA400.DataSource.1;Persist Security Info=False;User ID=admin;Data Source=s2127291
| Timeout: 30 seconds
| [X] Generate header
Prepare query
| Delimited
| Field delimiter [,]
| Text qualifier ["]
|
+--Output to file(s)
[ ] Only update date on changed files
[ ] Append mode
[ ] Change extension to: .txt
[ ] Open output file
Only output modified files
Backup mode
[ ] Remove empty output files

Files List
----------

Use the line below to remove common non-text files from website processing
.[ 'gif' or 'png' or 'jpg' or 'bmp' or 'avi' or 'ico' or 'mp3', lineEnd ]
Use the line below to remove common non-text folders from website processing
_vti