Source language: Translate to:

Flat File import possible?

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Flat File import possible?

Postby schmutly » Sun Aug 03, 2014 6:28 am

Hello.
I have a Flat File text document and wanted to know if there's anyway to do Flat File Mapping with DBPro?
I was thinking of npXMLS but i know you can import a csv file in but will it support the spacing(TABs) found in
a Flat file?
When i open the data.txt in notepad it looks exactly like this:
Image

Any ideas i can try?
Thanks,
Rob

PS:
Forgot important point...if i open Excel, click Data tab then click "from text" it gives that nice "Text Import Wizard"
which works perfect...but i want to create a util that doesn't require excel, at least NOT to do this part.
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Re: Flat File import possible?

Postby Gaev » Sun Aug 03, 2014 8:06 am

schmutly:

If the "Delimiter" is a "Tab", you can specify [#9] as the "Delimiter" like so ...
Code: Select all
dbpImportFromCSV "AddrBook" "Contacts" "[PubDir]data.csv" "Delimiter=[#9];ContainsFieldNames=Yes;MatchFieldNames=Yes"
User avatar
Gaev
 
Posts: 3736
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Flat File import possible?

Postby schmutly » Sun Aug 03, 2014 8:50 am

Thanks Gaev,
I'll try that in the morning.
Rob

Thanks Gaev,
I'll try that in the morning.
Rob

EDIT:
Thanks anyway but..didn't work, i didn't think it would either as its not evenly Tabbed
and not really a csv file and maybe need http://sourceforge.net/projects/ff-extractor/
to convert it first?!...
Says "Table and import file do not contain any matching fields" but they are there
The text file HEREif your curious,
only open in notepad and maximum...OR...clickHERE to SEE how its formated
When you try this with the Wizard in Excel it allows you to adjust where the
columns are to appear and if its in the correct position. Looks like ill have to
leave this project to another time and keep using excel.
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Re: Flat File import possible?

Postby dpayer » Mon Aug 04, 2014 7:01 am

schmutly wrote:Any ideas i can try?
Thanks,
Rob


Here is an idea (but I don't have any code to show you).

You can also set up an ODBC connection to the text file. I think you will want to remove the row that has column titles in it first.

Once linked to a ODBC you should be able to use the file for simple SQL statements. I do not know the limits of the TXT based ODBC link.

Here is some info from Microsoft: http://msdn.microsoft.com/en-us/library ... 38(v=vs.85).aspx

Here is the standard connection string:
Code: Select all
Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;
Extensions=asc,csv,tab,txt;


David P
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: Flat File import possible?

Postby Gaev » Mon Aug 04, 2014 10:08 am

Rob:

I looked at the contents of the file using a Hex Editor (one that shows the Hexadecimal values of each character in the file.

The line delimiters are 0D0A ([#13][#10] in NeoBook syntax).

There don't appear to be any field delimiters ... just a (variable) number of spaces (Hex:20 or [#32]) ... not sure if the original file has these values or if Firefox replaces Tabs with required number of spaces.

If you can somehow upload this file to a website from which it can be downloaded, I can examine its contents for you ... or you can email it to me as an attachment ... send me PM on the forum and I will give you my email address.
User avatar
Gaev
 
Posts: 3736
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Flat File import possible?

Postby dec » Mon Aug 04, 2014 11:49 am

Hello,

Gaev wrote:Rob:
If you can somehow upload this file to a website from which it can be downloaded, I can examine its contents for you ... or you can email it to me as an attachment ... send me PM on the forum and I will give you my email address.


I agree with this. ;) On the other hand, my npXmls plugin probably can't help here (since it's an XML parser) but maybe others of my plugins can do the job or help to do it.
.
Enhance your NeoBook applications!
.
58 plugins, 1131 actions and 233 samples
.
NeoPlugins website: www.neoplugins.com
.
User avatar
dec
 
Posts: 1663
Joined: Wed Nov 16, 2005 12:48 am
Location: Spain

Re: Flat File import possible?

Postby David de Argentina » Mon Aug 04, 2014 1:18 pm

Hi schmutly,

You can handle the text file using the text ODBC connection string

see: https://www.connectionstrings.com/textfile/

As your text file is fixed length, your connection string is:

Code: Select all
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;
Extended Properties="text;HDR=Yes;FMT=Fixed";


AND you need to define the associate schema.ini file in order to set all columns (name, type and length)

a sample of this schema.ini file is in: http://support.microsoft.com/kb/149090/es

or addapt the below sample as you need:

Code: Select all
 [Contacts.txt]
   ColNameHeader=True
   Format=FixedLength
   MaxScanRows=0
   CharacterSet=OEM
   Col1="First Name" Char Width 10
   Col2="Last Name" Char Width 9
   Col3="HireDate" Date Width 8


My defaulted cent,
David de Argentina
User avatar
David de Argentina
 
Posts: 1560
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Re: Flat File import possible?

Postby schmutly » Tue Aug 05, 2014 12:01 am

Thanks guys,
i'll try those out and let you know how i go.
Thank you,
Rob
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Re: Flat File import possible?

Postby schmutly » Tue Aug 05, 2014 6:00 am

Thanks heaps David :D That worked...
As you can see from screen shot and the first item
I need to adjust the Char sizes but that's GREAT :)
I am assuming i can EXPORT as a csv and that would open
fine in EXCEL afterward? Ill discover this later anyway but
this is a great start..wonderful,
Thanks again
Rob
Image
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Re: Flat File import possible?

Postby schmutly » Tue Aug 05, 2014 6:16 am

Cleaned it up now and it imports each line perfect but i cant edit any of them or delete them
and this error comes up
Image
so whats that mean and how (IF) can i fix that?
Thanks
Rob
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Re: Flat File import possible?

Postby dpayer » Tue Aug 05, 2014 6:30 am

schmutly wrote:Cleaned it up now and it imports each line perfect but i cant edit any of them or delete them
so whats that mean and how (IF) can i fix that?
Thanks
Rob


Check the settings of the ODBC connection. It may be set to only read the file. You need read/write.

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: Flat File import possible?

Postby schmutly » Tue Aug 05, 2014 7:05 am

Not sure where to find that setting David
dbpOpenDatabase "DAT2XLS" "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\;Extended Properties=[#34]text;HDR=Yes;FMT=Fixed[#34];"
dbpOpenTable "DAT2XLS" "data#txt" ""
Thats all i have for the connection and then it opens the table. ?
Rob
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Re: Flat File import possible?

Postby dpayer » Tue Aug 05, 2014 8:16 am

schmutly wrote:Not sure where to find that setting David
dbpOpenDatabase "DAT2XLS" "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\;Extended Properties=[#34]text;HDR=Yes;FMT=Fixed[#34];"
dbpOpenTable "DAT2XLS" "data#txt" ""
Thats all i have for the connection and then it opens the table. ?
Rob



In the control panel, in administrative functions, go to the data section and check the driver itself. It has settings associated with it.

David P.
David Payer
Des Moines, Iowa
USA
User avatar
dpayer
 
Posts: 1384
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Re: Flat File import possible?

Postby schmutly » Tue Aug 05, 2014 8:30 am

ok thanks David.
I will check that in the morning,
Rob
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Re: Flat File import possible?

Postby schmutly » Tue Aug 05, 2014 8:52 pm

Thanks again.Found it in the Connection String setup
so that's good.... (could not find anything in C panel about it)

Thanks again to all who helped, the 3 David's again :lol: ..and Gaev.

Image

Cheers,
Rob

why? Its for a friend who cant export from a 20 year old Video DOS program. It only "prints to file"
like the TOP text file..so this enables her to view it and sort it to print what she needs (the old DOS
program didnt allow this either...Good ol NeoBook to the rescue again (with its Guru helpers :mrgreen: )
FINAL:
Image
schmutly
 
Posts: 217
Joined: Fri Feb 20, 2009 4:16 pm

Next

Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest