I have been searching for a way of downloading my pipe delimited DB.txt file and opening it in a spreadsheet or database program so that i can make some global changes to the contents of a field or multiple records than upload the db.txt to the site.
If I could find a way to import pipe delimited into a database or spreadsheet i could put the new link in the field and past it down to the other records. This will also help when I want to add multiple records to the database or re index the data base off line.

Use either MS Excel or Access.

Regards,

Eliot Lee
http://anthrotech.com/

I have Tried Excell but it will not recognize | pipes as delimators and puts the entire record in one field

is there a certain way I should import .... i have tried a few ways with no such luck ...that is why i came to the forums.....
A Friend told me that microsoft is not pipe friendly

I often do what you are asking. In MS Excel, open the .db file as you would any other, you should get the text import wizard, select delimited, select next, then specify "other" and type the pipe. Select finihs.

Yep, use excel first! BUT you're asking for trouble if you're posting back to a unix system. I told my friend to do the same thing that these other folks uggested and 5-10 the db file gets wanked on by excel.

I think you have to save it as a plain text file or whatever.

I like to use another editor like BBedit to check to make sure the Excel specific information is gone from the text before I upload it. A slight hassle but when it's up it works for sure.

good luck

**************************************
on the pages in between ...

Thanks i was successful in getting it into ms Excell .... now i feel really stupid .... i have tried to save as every format but none will allow me to return it to pipe deliminated.... I even tried to save as tab delimited text with the intention of converting tabs to pipes, but when i opened the tab delimited TXT file in ms word I found that the saving had created xtra " in some cases and extra tabs in other.

There must be a simplier way to change a block of fields in a bunch of records

:-)

I really don't know how the others find this so simple. Like I said you save it to a format that a simple text editor can deal with. I like to save in the tab format. Word is a BIG NO-NO. Just as bad as going straight from Excel to your server. NT servers I have no clue.

Once in a plain simple text editor do a search and replace for all tabs to pipes. It's an extra step but very safe and cuts out a lot of the troubles.

good luck

**************************************
on the pages in between ...

I agree with QooQ that Excel as a database edit tool is not as simple as a flat text file. I recall problems with date formats in Excel, especially trying to get a 4 digit year. Only when the user is comfortable with Excel then maybe it works better. Advantages include sorting, extracting, general spreadsheet stuff. Going to MS word is never good. I have good luck with a cut and paste from excel to Notepad or other text editor. Then search and replace tab for pipe. I find the way to get the tab into the search field is to copy it from the text, and paste into the search box.

I use Excel as an interface between dbman and my wife, and she uses a Mac! To ask her to work within a flat file would require some hand-holding.

hey hey I'm a touchy Mac user ;-)

why don't you set up file maker for her ?? set a few fields (doesn't have to be pretty) then do an import like excel. Of course, with excel you can see the whole list but if you just pounding a way a few data things it wouldn't be that hard. And if you have a PDA (palm or palm compatible) you can run excel on it too now.

this was an idea I've been playing with for a while. One of these days when I have spare time... yeah right.

good luck you all

PS macs aren't that bad and neither are their users :-D


**************************************
on the pages in between ...

I'm a Mac-user and worked on something like this just today,

I took an Excel spreadsheet, brought it into Word and converted it to a table. Made sure everything lined up, moved some columns around, basic clean-up, then converted the table to text. Both conversions allow you to specify the "pipe" as the delimiter. Then Save As a text file.

Uploaded to the server and it worked great.

I tried editing in Excel, but had to export as tabs, bring into BBEdit, remove the excess quotes, replace tabs with pipes, and dbman didn't like it a bit. When I did the whole thing in Word, dbman said, "Yummy!"

FWIW.

zack

 
This macro will write an Excel file to a pipe delimited text file. I found it in this forum a long time ago and it works well. The only problem I have found is that you cannot have an empty cell at either the beginning or end of the record because it doesn't print the | and corrupts the DB. Make sure you have required fields in the first and last cell in each row. Empty cells in between are fine.


Sub OutputActiveSheetTextFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String

'ListSep = Application.International(xlListSeparator)
ListSep = "|"
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open "C:\DBMAN\default.db" For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End Sub

Tom

I'm not clear on the file make idea, what we do and why are as follows, I create an excel file from the DBman flat-file and email it to her, she edits the excel file (inventory) and emails it back to me, I make the new flat-file and FTP it to her site. The why is because she has 28.8 phone hookup at home. Originally I set up DBman so she could add, edit and delete from home through DBman, but doing one record at a time seemed to slow. I like the idea of both export DBman to excel and the macro to make the flat-file, then I may need to do nothing at all!

Have you any opinions on a Mac programs for her to use to FTP?

what OS and machine is she using? OS X will open up alot for different options, I'm exploring those right now.

up to OS 9.1 (9.2.1 will eventually eliminate all older macs .... looks like the end for pre-G3 macs :-(

I use anarchy/interarchy, never did like fetch. Ummm BBEdit has ftp built in.

With FileMaker (similar to Access) you could essentially imitate DBMAN. Excel like you mentioned is the quick and easy way to deal with multiple editing.

good luck

**************************************
on the pages in between ...

This is VERY easy to do without MS Word, etc.

Get yourself a good text editor, eg: Textpad.
Open your db file in the text editor.
Do a global search and replace: pipe to tab.
Cut the entire modified text.
Paste into a blank Excel worksheet.
Do your Excel thing.
Check Excel mangling, eg: spurious quotes, dates.
Copy the worksheet.
Paste back into your text editor.
Do a global search and replace: tab to pipe.
Save (in Unix format if you intend load back from Win to Unix).