Hello,

My system is Universe v10 running on Windows 2003
I have a CSV file called deals.csv that I would like to make into a
pick file called deals.mv

I have been searching the group, trying to help myself, but honestly
don't have the experience to make this work.

I was hoping I could obtain some source code that would permit me to
manually copy and paste the deals.csv file into my C:\IBM\UV folder,
then when I run the program, it creates a new PICK file called
deals.mv.

Each row of the CSV file would be a record in the deals.mv file, with
the name of the record being the very first field of each row in the
deals.csv file.

For example, the first row of deals.csv is
123456,starburst,bx,9.99,7950000232

I would like to turn that into a record in the deals.mv file entitled
123456 with the contents of the record being:

0001: 123456
0002: starburst
0003: bx
0004: 9.99
0005: 7950000232



I believe my CSV file is "clean" and does not have any quotes or LF/CR
in them. There are, however, spaces in some of the fields.

Thank you in advance for any tips or assistance.

Phil

To make your life easier, I'd open the file in Excel, then save as a
tab delimited file --> Excel does this well, and you will not have any
"issues" to deal with. Next, pop your file into a type 1/19 file
(directory) so that it can be accessed.

I'll assume that this file is called "DOS", the source item is called
"SOURCE" and your target file is deals.mv, and that there isn't a
"header" record for the SOURCE item


Code would be :

OPEN "","DOS" TO DOS.FV ELSE STOP "OPEN DOS FAILED"
OPEN "","DEALS.MV" TO DEALS.FV ELSE STOP "OPEN TARGET FAILED"
READ DOS.REC FROM DOS.FV,"SOURCE" ELSE STOP "SOURCE READ FAILED"

* MANY WAYS TO IMPROVE THIS LOOP, BUT FOR SMALL
* ITEMS THIS IS FINE

NOL = DCOUNT(DOS.REC,@FM) ;* number of lines (new
records)

FOR I = 1 TO NOL
ALINE = DOS.REC<I> ;* get a raw
line
CONVERT CHAR(9) TO @FM IN ALINE ;* put into UV format
WRITE ALINE ON DEALS.FV, ALINE<1> ;* write it out
NEXT I

STOP "ALL DONE"


hth



Wytevette wrote:

To get the CSV from an XLS file in the first place, you can use my
ExcelExport program. It's completely free, no binding to mv.NET or
any other for-fee communications product.
http:// removethisNebula-RnD.com/freeware/
The advantage here is that it exports all worksheets into unique CSV
files. See the related README doc.

Once you have the CSV files you can use code like what Ross suggests.
ExcelExport ensures that all fields are wrapped in quotes, so the code
would need to remove beginning and trailing quotes, and then convert
all embedded instances of two consecutive quotes into one double
quote. This just adds two more lines of code to what Ross provided.

BTW, the code below breaks fields based on tabs but your data example
shows fields are delimited by commas. You say your code doesn't
include commas or quotes but as soon as you hard-code that "fact" into
your program you'll find someone has inserted quotes or commas either
on purpose or on accident. Do yourself a favor an assume there may be
quotes and commas. This sort of pro-active coding is much better than
reactive "help, it just broke" programming.

Your need for CSV to MV import is really just about simple string
manipulation. One of the reasons why MV is so popular among us fans
is that MV BASIC is superb for this sort of operation - and it's very
easy to do. If you have any other programmers on staff they should be
able to work this out, or if you need something very specific there
are many people here who could write something for you, perhaps for
some low fee for their time.

Within the next week I intend to enhance the ExcelExport (with a
separate executable) to allow direct writing to an MV file exactly as
you have described. While this utility will be free, the
functionality requires mv.NET which has a small licensing fee. If
you're interested, let me know and you'll have this solution quickly
with no coding effort, full support, and you'll be able to use the new
connectivity to do a lot more.

HTH
Tony
TG@ removethisNebula-RnD.com


"Ross Ferris" wrote:

Hi Phil,

I'm not sure from whence all of the Excel references came when you
asked about CSV formated files - if you really want/need to use a
spreadsheet application as a filter OOCalc is more capable and
infinitely less expensive but:

If your CSV file is RFC compliant or even if "all fields are wrapped in
quotes", openQM has the ability to directly parse your file into either
a hashed MV file or a directory file. The directory file may well be UV
compatible but if not you could easily do a T-DUMP and simply T-LOAD
into UV.

An openQM license is vaguely equivalent to the cost of an Excel license
and there is no need to import into Excel; export as another delimited
format or install anyone's adware and the required transport layer
license.

Hmmmm ... come to think of it, you could even replace UV with openQM
and then wouldn't require any of those third party bits and their
complication - might make more sense than a mediocre spreadsheet
application or "free" adware as filters.
-Tom

Thanks for the "advert" Tom!

For those who have not explored OpenQM, we have specific operations to
generate and read CSV files in the query processor and in the QMBasic
language.

Martin Phillips, Ladybridge Systems

"Tom deL" <ted (AT) blackflute (DOT) com> wrote:
LOL. Now that you mention it, I'll admit it was silly for me to
assume that a CSV file came from Excel. But I think it's really funny
to imply that someone should buy a completely different database
product to import a text file into Universe.

Hey, why stop at replacing the database, replace the spreadsheet tools
too! Does that solve the CSV problem? No, but it sure sounds like a
plan!

Seems a bit overly zealous, no? A standalone package of Excel 2003
can be purchased for about $170. That's hardly a large sum of money
for software that some companies run their businesses on (no advocacy
of that practice implied), and hardly an amount to be associated with
the terms "infinite" or "expensive".

Sounds like a Rube Goldberg to me.

Did you forget the point you just made that Excel may not be involved?

What the hell are you going on about?
- There are no ads in my freeware.
- The freeware I suggested to export worksheets to CSV does not have a
transport layer into MV, and neither does anything you suggested to
replace it.
- No one said anything about importing into Excel or exporting in any
other format.
- You compare QM to my freeware as though they do the same thing. QM
has no Excel interface and doesn't write into other MV databases.
You're trying to make some kind of argument that X is better than Y
when the two concepts aren't even related.
- Haven't you lost some perspective about exactly what it is that
you're fighting for when someone already has data and a database and
you propose replacing everything they have just so they can do some
text manipulation?

Tom, I don't understand why you feel a need to persist with innuendo
and flat out lies when you reply to my postings. This doesn't help
anyone here. Like anyone else here, I provide free info and some free
software. I do what I can toward the common good. When someone wants
something special that saves them time, improves their life, or allows
them to make more money, I offer to sell my solutions. There's
nothing evil about that but it seems you get bent when I offer to sell
solutions. Maybe some hatred for Microsoft drives your responses. I
really have no idea what your motivations are but I wish you'd get it
out in the open.

What's really a shame is when week after week we see people hunting
for the same solutions, expecting to find them for free, solutions
that have been available (perhaps for years) for a very low and
reasonable cost. How much time=money is spent in the quest for all
things free? How much time=money would be saved if people just took a
look at some of the for-fee solutions that I offer whenever they ask
for help. How much money might some developer have earned in the last
year if only they would have adopted and sold a for-fee solution,
rather than waiting an entire year with no related income for some
free magic bullet? How do you help these people when you try to
undermine my efforts to provide solutions - even the free ones?

Hi Phil

If your csv is large, it wil be quicker to read in each line using
openseq then readseq. You can then split the line on the commas - but
be carefull as the column data may have commas as well which is why
many people say tab delimited is better.

I have some free databasic code to do all this including checking for
commas and double quotes inside a quoted column, if you want it i can
contact you off group.


rgds
Symeon

* THIS works in universe for unix. Check OPENSEQ and change the file
location
*

OPENSEQ '/xfer/file.txt' TO SEQIN ELSE

* is suspect for windows you would us 'c:\dir\file.txt\' instead

CRT 'CANT OPEN SEQ FILE'

STOP

END

OPEN 'DEALS.MV' TO DEALS ELSE

CRT 'CANT OPEN DEALS.MV'

STOP

END

C=1

*

LOOP

READSEQ LINE FROM SEQIN ELSE EXIT

CONVERT ',' TO @AM IN LINE

* do any data conversion here before write

WRITE LINE ON DEALS, C

C += 1

REPEAT

END


HTH
Rob

(sorry for all the additional spaces. I cut and pasted from my telnet
program and didnt see the extra lines)

I really think its bad form to read an entire text file as a record
into UV. What if the file had a million rows?
DCOUNT then looping through that item would take many, many times long
than using OPENSEQ and reading in a row at a time.
I didnt notice if the orignal poster indicated the size of the file, so
this wouldnt be a problem if it were hundreds or even a few thousands
of lines.
M2C
Rob

Another thought: Excel is limited to 65K rows.
The national do not call registry that I imported in 3/2006 was a
55,000,000 row text file. Only way to do that is with OPENSEQ.
Excel is a great way if you want to do a bunch of calculations then
import to mv, but I avoid it as a transport method when I can.