schema.ini Pipe Delimited to Access Database

Fordraiders

vb.net 2003
Office 2003

What I have:
C:\TestData\Inp ut.txt
Text File Pipe Delimited : 4 columns Of data
example:
00001|NO BRAND NAME ASSIGNED|6DU27| M3-.5 X 6 FLAT HD SOCKET CAP SCREW,
CL10.9, ALLOY STEEL
00002|NO BRAND NAME ASSIGNED|6DU28| M3-.5 X 8 FLAT HD SOCKET CAP SCREW,
CL10.9, ALLOY STEEL
00003|NO BRAND NAME ASSIGNED|6DU29| M3-.5 X 10 FLAT HD SOCKET CAP SCREW,
CL10.9, ALLOY STEEL
00004|NO BRAND NAME ASSIGNED|6DU30| M3-.5 X 12 FLAT HD SOCKET CAP SCREW,
CL10.9, ALLOY STEEL
00005|NO BRAND NAME ASSIGNED|6DU31| M3-.5 X 14 FLAT HD SOCKET CAP SCREW,
CL10.9, ALLOY STEEL
00006|NO BRAND NAME ASSIGNED|6DU32| M3-.5 X 16 FLAT HD SOCKET CAP SCREW,
CL10.9, ALLOY STEEL
00007|NO BRAND NAME ASSIGNED|6DU33| M3-.5 X 18 FLAT HD SOCKET CAP SCREW,
CL10.9, ALLOY STEEL
00008|NO BRAND NAME ASSIGNED|6DU34| M3-.5 X 20 FLAT HD SOCKET CAP SCREW,
CL10.9, ALLOY STEEL
00009|NO BRAND NAME ASSIGNED|6FB98| M3-.5 X 20 SOCKET SET SCREW, CUP PT.,
DIN 916, BLACK
00010|NO BRAND NAME ASSIGNED|1CB70| 5/8 X 2" STANDARD SOCKET SHOULDER
SCREW, 1/2-13 THRD, ALLOY

Access Database:
C:\TestData\Inp utData.mdb
1 Table
ImpData: 3 Fields
All text:
ImportID -Text 15
Mfgname - Text 100
Gnum- Text 25
Desc- Text- 255

What I need:
I need a procedure that will import the "Input.txt" into the Access
Table

'' current code

Function ImportTextToAcc ess() As Boolean

Dim AccessConn As New
System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;Data
Source=c:\dm200 7\ImpData.mdb")
AccessConn.Open ()
'New table
' This sometimes works but the the delimiter is not working correctly
at all
Dim AccessCommand As New System.Data.Ole Db.OleDbCommand ("SELECT
* INTO [InpTable2] FROM
[Text;DATABASE=C :\DM2007;HDR=no ;FMT=Delimited( |)].[INPUT.txt]",
AccessConn)

' This does not work at All.... ?
'Existing table
' Dim AccessCommand As New
System.Data.Ole Db.OleDbCommand ("SELECT * INTO [InpTable] FROM
[Text;Database=c :\dm2007;Hdr=No ;Fmt=Delimited( |)].[].[Input.txt]",
AccessConn)

AccessCommand.E xecuteNonQuery( )
AccessConn.Clos e()

End Function

Thanks
fordraiders
fordraiders

--
Sent via .NET Newsgroups
http://www.dotnetnewsgroups.com

Dec 22 '06 #1

Subscribe Reply

8183

Paul Clement

On Thu, 21 Dec 2006 18:09:21 -0800, Fordraiders <st****@insight bb.comwrote:

¤ vb.net 2003
¤ Office 2003
¤
¤ What I have:
¤ C:\TestData\Inp ut.txt
¤ Text File Pipe Delimited : 4 columns Of data
¤ example:
¤ 00001|NO BRAND NAME ASSIGNED|6DU27| M3-.5 X 6 FLAT HD SOCKET CAP SCREW,
¤ CL10.9, ALLOY STEEL
¤ 00002|NO BRAND NAME ASSIGNED|6DU28| M3-.5 X 8 FLAT HD SOCKET CAP SCREW,
¤ CL10.9, ALLOY STEEL
¤ 00003|NO BRAND NAME ASSIGNED|6DU29| M3-.5 X 10 FLAT HD SOCKET CAP SCREW,
¤ CL10.9, ALLOY STEEL
¤ 00004|NO BRAND NAME ASSIGNED|6DU30| M3-.5 X 12 FLAT HD SOCKET CAP SCREW,
¤ CL10.9, ALLOY STEEL
¤ 00005|NO BRAND NAME ASSIGNED|6DU31| M3-.5 X 14 FLAT HD SOCKET CAP SCREW,
¤ CL10.9, ALLOY STEEL
¤ 00006|NO BRAND NAME ASSIGNED|6DU32| M3-.5 X 16 FLAT HD SOCKET CAP SCREW,
¤ CL10.9, ALLOY STEEL
¤ 00007|NO BRAND NAME ASSIGNED|6DU33| M3-.5 X 18 FLAT HD SOCKET CAP SCREW,
¤ CL10.9, ALLOY STEEL
¤ 00008|NO BRAND NAME ASSIGNED|6DU34| M3-.5 X 20 FLAT HD SOCKET CAP SCREW,
¤ CL10.9, ALLOY STEEL
¤ 00009|NO BRAND NAME ASSIGNED|6FB98| M3-.5 X 20 SOCKET SET SCREW, CUP PT.,
¤ DIN 916, BLACK
¤ 00010|NO BRAND NAME ASSIGNED|1CB70| 5/8 X 2" STANDARD SOCKET SHOULDER
¤ SCREW, 1/2-13 THRD, ALLOY
¤
¤ Access Database:
¤ C:\TestData\Inp utData.mdb
¤ 1 Table
¤ ImpData: 3 Fields
¤ All text:
¤ ImportID -Text 15
¤ Mfgname - Text 100
¤ Gnum- Text 25
¤ Desc- Text- 255
¤
¤ What I need:
¤ I need a procedure that will import the "Input.txt" into the Access
¤ Table
¤
¤ '' current code
¤
¤ Function ImportTextToAcc ess() As Boolean
¤
¤ Dim AccessConn As New
¤ System.Data.Ole Db.OleDbConnect ion("Provider=M icrosoft.Jet.OL EDB.4.0;Data
¤ Source=c:\dm200 7\ImpData.mdb")
¤ AccessConn.Open ()
¤ 'New table
¤ ' This sometimes works but the the delimiter is not working correctly
¤ at all
¤ Dim AccessCommand As New System.Data.Ole Db.OleDbCommand ("SELECT
¤ * INTO [InpTable2] FROM
¤ [Text;DATABASE=C :\DM2007;HDR=no ;FMT=Delimited( |)].[INPUT.txt]",
¤ AccessConn)
¤
¤
¤
¤
¤ ' This does not work at All.... ?
¤ 'Existing table
¤ ' Dim AccessCommand As New
¤ System.Data.Ole Db.OleDbCommand ("SELECT * INTO [InpTable] FROM
¤ [Text;Database=c :\dm2007;Hdr=No ;Fmt=Delimited( |)].[].[Input.txt]",
¤ AccessConn)
¤
¤ AccessCommand.E xecuteNonQuery( )
¤ AccessConn.Clos e()
¤
¤ End Function
¤

Any delimiter other than a comma requires a schema.ini file. Below is an example of what the
contents would look like:

[Input.txt]
ColNameHeader=F alse
CharacterSet=AN SI
Format=Delimite d(|)

In addition, if the destination table already exists you need to use INSERT INTO...SELECT.. .FROM:

INSERT INTO [InpTable2] (ImportID, Mfgname, Gnum, Desc) SELECT F1, F2, F3, F4 FROM
[Text;DATABASE=C :\DM2007;HDR=No].[Input.txt]
Paul
~~~~
Microsoft MVP (Visual Basic)

Dec 22 '06 #2

Similar topics

2619

Newbee - Schema concept question

by: cmc | last post by:

I need some clarification to help me understand the DB2 strucure more. The questions are about "implicit schema" 1. This is a very interest concpet that DB2 let every user to create new schema (as this is part of the PUBLIC group privilege - if I am not wrong). From a practical stand point, what is the application of such concept. 2. Suprisingly, if the schema is an implicitly created, everyone else can create objects in it too. What...

DB2 Database

3677

importing an RTF comma delimited file

by: sparks | last post by:

I have never done this and wanted to ask people who have what is the best way. One person said import it to excel, then import it into access table. but since this will be done a lot, I am trying to avoid this extra step. can access read in this file directly into a table as append data? thank you very much for any pointers

Microsoft Access / VBA

9733

Help with schema.ini file

by: mollyf | last post by:

Hello all-- I'm working on a legacy Access 2000 application which imports a space delimited text file. There's a line of code: DoCmd.TransferText acImportFixed, "ClaimSpecsEDS", "ImportedClaims", Me!txtLocation which I know is importing the text file. From reading, I know that the "ClaimSpecsEDS" referred to in the line of code is the specification

Microsoft Access / VBA

2319

Why is StringBuilder changing pipes to "o" umlaut when loading a pipe-delimited string?

by: Ray Stevens | last post by:

I am loading a pipe-delimited string from a DataSet into StringBuilder, such as 00P|23423||98723 (etc.). For some reason the pipe character is displaying in the debugger as "o" with two small dots on top (i.e., with an umlaut). Is StringBuilder modifying my pipe character and, if so, how do I stop it?

C# / C Sharp

10600

Find out the schema with psycopg?

by: survivalist | last post by:

I am trying to discover the schema of a PostgreSQL database programatically at runtime. I'm using psycopg (I assume that's still the best library). Is there a way to query the schema other than opening a system pipe like "psql -d '\d'", "psql -d '\d tablename'", etc.? DBIAPI 2.0 shows that the Cursor object has a .description method that describes rows, and there's the Type object. But neither of these appear to give you table names.

Python

3656

assign pipe delimited file to hash??

by: bhups | last post by:

hi, i am doing a lookup using hash table. I have a pipe delimited file that i have to search for a new invocie number field by checking of an old invoice number-customer number key combination exists in hash: sample input file: === 1wwwwwwwwwwwwhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh0 4wwwwwwwwww00002635168A0000C10139hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh9 ===

Perl

12256

Import tab delimited multiple files using VBA

by: Pete | last post by:

I need to import multiple tab delimited files for which I do not have the formats. All files have column headers in the the line. The files must be dynamic. The only common value is that the files are text and use tabs to delimit the column data. I can not use the File/Import as people using the program do not have the necessary experience to perform this function. Therefore, I need to programtically create unique tables for each...

Microsoft Access / VBA

8559

Export pipe delimited TEXT file

by: bhapate1 | last post by:

Hi all, I Have a Table with 10 Columns, Among those i have Data in just 3 Columns. Rest of columns wil be blank. I want to Export that Table as a Pipe Delimited Text File. I am using "TransferText" Action in Macro. It gives me Export Type like "Export Delimited" But in that out put i am getting " (Double Quotes) & , (Commas). I want Filtered Output. I just need Text and Pipes in final Output. Is there any other way to ge the Expected...

Microsoft Access / VBA

2325

MSSQL2000: PIPE Delimited Issue

by: benchpolo | last post by:

Data Row 1 -> AAA|123|ABCDE|929292||183 EAST WAY #A11|223|TX|CA|12 Data Row 2 -> BBB|123|ABCDE|||183 EAST WAY #A11|223|TX|CA|12 Data Row 3 -> VVV||ABCDE|929292||183 EAST WAY #A11|223|TX|CA|12 Data Row 4 -> AAA|123|ABCDE|929292||183 EAST WAY #A11|223|TX|CA|12 Data Row 5 -> WWW|123|ABCDE|929292|QA|183 EAST WAY #A11 |223|TX|CA|12 Data Row 6 -> AAA|123|ABCDE|929292||183 EAST WAY #A11|223||CA|12 Data Row 7 -> KKK|123|ABCDE|929292||183 EAST WAY...

Microsoft SQL Server

8683

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8609

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9170

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8901

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8871

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7739

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6528

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

3052

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2336

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP