Convert .TXT --> .MDB

I have a .txt file in the following form :

-------------------------------------------
text1
-------------------------------------------
text2
-------------------------------------------
text3
-------------------------------------------
..
..
..
-------------------------------------------
text n
EOF

How can convert this file in a .mdb file
with one field and with n records (text1,text2,...,text n) ?
I need one only field and eatch record has the text between the lines.



0
giannis
8/20/2003 2:39:49 PM
access.conversion 3037 articles. 0 followers. Follow

3 Replies
464 Views

Similar Articles

[PageSpeed] 3

I dont speak english very well but lines"---------" dividing the
different texts (txt1,txt2...) [txt1 differs from txt2 because of
these lines].

"John Nurick" <j.mapSoN.nurick@dial.pipex.com> wrote in message
news:fs5akv865ca0sp3v3bbfq69d0pm27a91j3@4ax.com...
> In that case I don't understand what you want. What do the
> -------------------- lines signify?
>
>
> On Thu, 21 Aug 2003 10:17:10 +0300, "giannis" <zinobios@hol.gr> wrote:
>
> >My problem isnt delete ------ lines but I need
> >text1=record1, text2=record2,....,text n=record n
> >
> >"John Nurick" <j.mapSoN.nurick@dial.pipex.com> wrote in message
> >news:4qh7kv4lm1m0775u2hlvp14c367ee95bdo@4ax.com...
> >> Hi Giannis,
> >>
> >> If I understand the situation right you can use the Windows FIND.EXE
> >> utility to eliminate the --------------------- lines and import the
> >> resulting file. At a command prompt, use something
> >>
> >> FIND /V "-------------" "D:\Folder\Text file.txt" > "New file.txt"
> >>
> >> If Perl is installed on your computer, a command like this will do the
> >> job:
> >>
> >> perl -ne "print unless m/^\-+$/" filename.txt > newfile.txt
> >>
> >> Alternatively, import the file as is, and then use a delete query to
> >> delete all the records that contain ----------------.
> >>
> >> On Wed, 20 Aug 2003 17:39:49 +0300, "giannis" <zinobios@hol.gr> wrote:
> >>
> >> >I have a .txt file in the following form :
> >> >
> >> >-------------------------------------------
> >> >text1
> >> >-------------------------------------------
> >> >text2
> >> >-------------------------------------------
> >> >text3
> >> >-------------------------------------------
> >> >.
> >> >.
> >> >.
> >> >-------------------------------------------
> >> >text n
> >> >EOF
> >> >
> >> >How can convert this file in a .mdb file
> >> >with one field and with n records (text1,text2,...,text n) ?
> >> >I need one only field and eatch record has the text between the lines.
> >> >
> >> >
> >>
> >> John Nurick [Microsoft Access MVP]
> >>
> >> Please respond in the newgroup and not by email.
> >
>
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.


0
giannis
8/22/2003 7:16:16 AM
Thanx very much !!!!!! This is the ansewer!!!!
I create a table "Table1" with a field "p" as memo
and i import the text.
Can you write a query so update the symbol "#"
with the chr(13) ? I want it in SQL form (UPDATE ....) ?
I am new in SQL and i want this for a example.

"John Nurick" <j.mapSoN.nurick@dial.pipex.com> wrote in message
news:cc6akv4eb7calqrk8ef733cdo4iqb5s6fa@4ax.com...
> On Thu, 21 Aug 2003 17:21:29 +0300, "giannis" <zinobios@hol.gr> wrote:
>
> >You dont understand. After importing i have (n records)x(mass of lines at
> >eatch text)
> >(because eatch text contains >1 lines !!!)
>
> This implies that your textfile looks like this:
>
> ----------------------------
> blah blah
> blah stuff blah blah blah
> ----------------------------
> blah blah thejhreh jkjk
> blah stuff blah blah blah
> blah kjkjkkjlj
> ----------------------------
> blah blah trttr
> blah
> ajhklj
> uhiuhas
> ashuio
> ----------------------------
> blah blah
> blah stuff blah blah blah
> blah
> ----------------------------
>
> One simple way to deal with this is:
>
> 1) in a text editor (or in Word) replace all linebreaks with a tag that
> does not appear anywhere in the data, for example "###"
> The beginning of the file will now look like this:
>
> ----------------------------###blah blah###blah stuff blah blah
> blah###----------------------------###blah blah thejhreh jkjk###blah
> stuff blah blah blah###blah kjkjkkjlj###----------------------------
>
> 2) now replace all instances of ----------------------------### with a
> linebreak, resulting in
>
> blah blah###blah stuff blah blah blah###<linebreak>
> blah blah thejhreh jkjk###blah stuff blah blah blah###blah
> kjkjkkjlj###<linebreak>
>
> 3) import this into an Access table
>
> 4) restore the linebreaks by using an update query to replace ### in
> each record with Chr(13) & Chr(10)
>


0
giannis
8/22/2003 10:15:15 AM
On Fri, 22 Aug 2003 13:15:15 +0300, "giannis" <zinobios@hol.gr> wrote:

>Thanx very much !!!!!! This is the ansewer!!!!
>I create a table "Table1" with a field "p" as memo
>and i import the text.
>Can you write a query so update the symbol "#"
>with the chr(13) ? I want it in SQL form (UPDATE ....) ?
>I am new in SQL and i want this for a example.

If you are using Access MDB the query will look like this: 

UPDATE Table1 
  SET p = Replace([p], "###", Chr(13) & Chr(10))
  WHERE p Is Not Null;

If you are using Access 2000 you can't use the Replace() function in a
query. Instead you have to use a custom VBA funtion to call Replace(),
and call this function in a query. So you put something like this

Public Function MyReplace(S As String) As String
  My Replace = Replace(S, "###", vbCrLF
End Function

in a VBA module and use a query like this

UPDATE Table1
  SET p = MyReplace([p])
  WHERE p Is Not Null;



John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
0
John
8/22/2003 9:14:52 PM
Reply:

Similar Artilces:

converting money 2002 files to money 2005
I recently bought a new pc w/vista. Attempted trial w/money essentials which does not recognize previous money files. downloaded money 2005 free trial version. all i needed was to balance my statements. tried to look at reports. had to download updates. once downloaded, error message received "not enough memory is available" . well, I bought money 2005 disk off ebay. it will not recognize my backup file. finally got it to recognize one of my backups and again, i get the same "not enought memory . . ." This is very irritating and discouraging. I don't know what&#...

Aunsoft iMedia Converter for Mac 1.0 for blu-ray and video
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Aunsoft iMedia Converter for Mac 1.0 for blu-ray and video <br><br>Aunsoft iMedia Converter for Mac is the Mac application for Blu-ray movie and video lovers to easily share movies and recorded special moments on home theatre systems without the trouble of video formats. The iMedia Converter is a universal converter for Apple computers like iMac, MacBook. It is a combination of a Blu-ray ripper and a video converter. <br><br>Working as a Blu-ray ripper, you can insert home-made blu-ray vi...

Convert From Column to Row (Record)
I have a table: ID Price Quantitiy 1 1.00 2 I would like to create a make table query to convert the original table to something like this: ID Measure Data 1 Price 1.00 1 Quantity 2 Please help, hopefully ther is a simple solution!!! Look up Excel Paste Special - Transpose for a solution. Or try this --- SELECT "Price" AS Measure, [URTable].[Price] AS Data FROM URTable UNION ALL SELECT "Quantity" AS Measure, [URTable].[Quantity] AS Data FROM URTable; -- KARL DEWEY Build a little - Test a little "banker123&qu...

Convert Access table to SQL
i have 2 access files of Forms and tables. The tables are now successfully imported into SQL 2000 server. Forms are still saved in the Access file. i created back ODBC links in the Forms (Access file). Very strange, it only happens in one of the tables. i open the table (Access file) linked by ODBC to SQL i can amend the old records. when i create new record, i can insert but after insert, i cannot amend those new insert records. a message come out about write conflict, telling me about another users using it. i am sure i am only the user. Grateful for any advice. thx. tony ...

converting outlook 2003 into outlook 2002
How do you import a .pst folder created in Outlook 2003 into a computer with outlook 2002. I'm getting an incompatible message. Is there a download for conversion No. You must expressly create a "New Personal Folders file" in "Outlook 97-2002" format while in Outlook 2003 and copy your data into it. -- Russ Valentine [MVP-Outlook] "bonni" <anonymous@discussions.microsoft.com> wrote in message news:073801c3d790$dc29b850$a401280a@phx.gbl... > How do you import a .pst folder created in Outlook 2003 > into a computer with outlook 2002. I'm gett...

Help needed converting publisher files to pdf
When I save a publisher file to pdf format the colours of my fonts sometimes change. I've found this with red and pink colours - they come out grey in pdf format. Why is this? What can I do to prevent this change from happening?? Thanks, Debbie What .pdf making program are you using? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Debbie" <Debbie@discussions.microsoft.com> wrote in message news:37DB3FD6-0A83-4812-8537-0A7E0EDBE4D7@microsoft.com... > When I save a publisher file to pdf format the c...

Date format converted from UK to US format
I have a workbook with multiple tabs, each of which take data from mutlipe spreadsheets using a Query. I then use the code below, to consolidate all the data on the tabs into one table, so i can PivotReport on it. The problem comes from the columns with dates in it - they import using the Query in UK format, but after the code has brought the tabs into one, they become US format as text. Can anyone help correct this, as i'm having to use a workaround formula to rearrange the cells at the moment. Sub collate2() Dim firstRow As Double, lastRow As Double, srcRng As Range,...

Can't Convert Money 99 File
Money 2002 will not convert my 99 file. Tried to restore from back-up onto new compter - that crashed Money 02. Also tried to install 02 on old machine over money 99. Same thing - Money 02 crashes. Data is not corrupted. It works fine in newly reinstalled 99. Read MS Knowledge Base article 316955 regarding unrecorded over due bills. not the case here. What gives?? If you can create a new file in Money 02 or create a new file in 99 and convert the new file to 02, your 99 file is likely corrupted. Although you may not see any symptoms in 99, sometimes the corruption surfaces upo...

mdb to txt files
Problem -> I have several thousand db files created by Access that I need to convert to delimited plain text. Add to that that I'm a unix/perl hack with next to zero development time on windows platforms (and hence little clue about coding in VB). What I'd like to do is be able to write a script to recursively go through a directory, looking for mdb files, and convering them to tab delimited text (with the header). I can handle everything by the final part with Perl. I've figured out how to do this in Access with TransferText Macros, but need a solution with a lot less mousi...

Rule to auto open a txt file and search for a keyword
I am running Outlook 2003 on Exchange 2003. We are using a Unix based program that forwards faxes to certain email accounts that our users check. I need outlook to open the email then open a txt file and search for : Normal or : Maxtries. If it finds : Normal then move to a folder called Normal. If it finds : Maxtries then move to a folder called MaxTries. Does anyone know how to go about doing this or have an idea that I can try. Thank you in advance for your help. are you doing this on outlook or exchange? do you want a ready to use solution or do you plan to program it yourself? i...

Convert text file to MS_Excel
I have a text file created and wish to create a MS-Excel spreadsheet w/columns the 1st column should be static while the row could have variable data to append. Any ideas/scripts to share will be appreciated. Sample File: Client: PSHBGMCGAPP01 Backup ID: PSHBGMCGAPP01_1101705374 Policy: McGregorServersDailyBackup Policy Type: MS-Windows-NT Sched Label: MonthlyFull Schedule Type: Full Backup Retention Level: 6 months (6) Backup Time: 11/29/2004 00:16:14 Elapsed Time: 000:36:58 Expiration Time: 06/03/2005 01:16:14 Compressed: n...

Changing text on convert lead webpage dialog
In the Opportunity form, I have renamed the "Potential Customer" Attribute to "Reseller". But on the Convert Lead webpage dialog, under Opportunity - the lookup field still displays as "potential customer". Is there anyway of changing this text? Hello, I think this webpage dialog is not customizable using the standard tools. I remember having a customer wanting to add functionality to the convert lead webpage. If someone has an idea about this would be nice. Best regards, Merijn van Mourik "kwhite" wrote: > In the Opportunity form, I have ...

Converting from Money 2000 to Money 2006
I just bought a new laptop that had money 2006 installed. My desktop still has money 2000 which is what we've been using. I've copied the files over and they work fine - I just can't get a feature that was available in Money2000 to work in 2006. From the 'Pay Bills' screen in 2000, it will show you the balance today, and after you pay the selected bills. I can't seem to find anything like this in Money 2006. It is very helpful when we are paying our bills and want to pay a little more than normal. Any ideas? Have you looked at Forecast Cash Flow? See http://um...

converting numbers to text
Hi, is there a formula that I can use to convert numbers to text? similar to =value(cell#) but instead convert number to text? Thanks Hi Igor, Igor T wrote: > is there a formula that I can use to convert numbers to > text? > > similar to =value(cell#) but instead convert number to > text? Yes - and interestingly enough, it's named TEXT. <g> Look up TEXT Worksheet Function in Excel help for more info on how to use it. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Hi Igor if you want...

Convert $ to actual numbers in an imported web query
I use the Web Query function to import tables with data from the internet to my spreadsheet. However, some of the amounts imported are in $ format (e.g. "$7,950.00") and do not function as numbers, i.e. I cannot perform mathematical calculations on them. How do I import $$ values as actual numbers? Thanks for your help. If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want ...

Converting an Activity to Oppty
Hi, We convert phone calls into opportunities in CRM. However, when converting, the opportunity takes on the potential customer as the contact instead of the account. Does anyone know how to change this mapping? Thanks K By default, opportunities tied to a Contact are also assocaited to the Account. If the activity were associted to the Account, the converted Opportunity would default to the Account. -- Matt Parks MVP - Microsoft CRM "BRM-CRM" <BRMCRM@discussions.microsoft.com> wrote in message news:519CF881-168A-45DB-9AC2-82CE0D661F4A@microsoft.com... Hi, We c...

Converting numeric data in Spreadsheet to zero
Hi I am working on a excel spreadsheet. It contains text dat and numeric data. what I am looking for is that I need to convert that numeric data into 0. I mean there are cells which contains values like 234, 22, 12 etc and they blank cells in between them. So is there a way by writing a command or Macro to convert that numeric data to 0, ie each field must contain a value 0 .. Thank you very much in advance Deepak -- deepakgoyal ------------------------------------------------------------------------ deepakgoyal's Profile: http://www.excelforum.com/member.php?action=getinfo&useri...

Convert time to a number when linking?
I'm referencing a time value in Worksheet "A" from the Data worksheet "B" like this; =Data!A1. Cell A1 is formatted as time and shows as 01:00, and I need it to be a general number (just a 1) in Worksheet "A" so I can divide it by another general number. Cells B1 & B2 are formatted as general in the source worksheet. In other words, the difference between 1:00 (A1) and 8:00 (A2) is 7. At 1:00 the temp was 32 (B1) degrees and at 8:00 the temperature was 152 (B2) degrees. In these 7 minutes the temperature rose 120 degrees. What I'm trying t...

Convert decimal to time help.
Is there a way if I key 3.30 in A1 can that be converted to 3:30 in B1? If that can be done how can I add time in excel eg 3:30 + 2:15 =5:45. Can this be done if the time is also more than 24:00. Help Please =INT(A1)/24+(MOD(A1,1)*100)/1440 or =TIME(INT(A1),MOD(A1,1)*100,) might need to reformat to hh:mm or [hh]:mm if it adds AM/PM Finally you can format as [hh]:mm for times greater than 24:00 -- Regards, Peo Sjoblom "Floyd Forbes" <newsgroups@omcast.net> wrote in message news:XtednVhF7cYrdjTVnZ2dnUVZ_g2dnZ2d@comcast.com... > Is there a way if I key 3.3...

Convert a Word rtf file into excel
Hi, I am trying to get data out of an rtf file into excel, however it is putting all information into 1 column,... Anyway to spread the data across multiple columns just like it looks in the rtf file? Please let me know. Thanks, Joan Open the rtf file in WordPad and save it as txt. Open Excel and then from Excel: Data > Open External Data... > Open Data and the submit the filename Tell the Import Wizard how to separate the fields. -- Gary''s Student - gsnu200767 "colorado808" wrote: > Hi, > > I am trying to get data out of an rtf file into exce...

Converting from WinHelp to Html Help
Our application currently uses WinHelp. I would like to know how to invoke html help from our MFC application. Are there any good ideas on how to go about this? Marathoner "Marathoner" <rajk2000@msn.com.invalid> wrote in message news:uMheMcTxFHA.2076@TK2MSFTNGP14.phx.gbl... > Our application currently uses WinHelp. I would like to know how to invoke > html help from our MFC application. Are there any good ideas on how to go > about this? > > Marathoner > > Try posting in microsoft.public.helpauthoring Marathoner, > Our application current...

Convert Date given by outlook api to java date
hi , i have following code _ContactItem contactItem; COleException e; OleInitialize (NULL); _Application olApp; MAPIFolder pFolder; if (!olApp.CreateDispatch(_T("Outlook.Application"), &e)) { CString strError; strError.Format(_T("CreateDispatch() failed with error 0x%08lx"), e.m_sc); //AfxMessageBox(strError, MB_SETFOREGROUND); OleUninitialize (); return 0; } _NameSpace oNameSpace = olApp.GetNamespace(_T("MAPI")); pFolder=oNameSpace.GetDefaultFolder(10); _Items Items; _Items pItems= pFolder.GetItems(); pContact = Items.GetFirst (); long l = pC...

Converting Spreadsheet to Table in Access?
Hello - I am performing an exercise in futility (I think). I have been able to migrate data from an old MS Works file to a new MS Access 2000 table, and now I'd like to do the same with data I have in a spreadsheet. Does anyone have any experience with this kind of thing? Thanks! Hi this is more a MS Access question but you can either linka spreadsheet as table or use 'File - Import' in MS Access and choose your Excel table as data so�rce -- Regards Frank Kabel Frankfurt, Germany "Bonnie" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:...

Converting Outlook .pst files to Mac
I bought a Mac when my Dell laptop crashed, but only have .pst files for my Outlook email, contacts, and calendar. I copied the .pst files to my Mac desktop but need to import them into Mac Mail and Address Book programs. I see third-party software that requires running Outlook (I can't because the Dell hard drive is dead) or using csv files (again, I can't export in that format because the PC is dead). Does anyone know how I can get the data in my Outlook .pst files into my new Mac programs? Many thanks, Jason Hi Jason: See here: http://scriptbuilders.net/files/exportimportentour...

Excel 2007 Disable Convert Button
Hi, Iw ould like to disable the convert option that appears in Excel 2007 just above the save option. There is a GPO called "Disallow conert document (Excel, PowerPoint, Word)", but that only works for Word, for PowerPoint they released a hotfix (948943), but for Excel there is no hotfix. Is there another way to remove/grey out that option? Thanks in advance! Pieter Vercammen ...