Help Convert File
SFBoater
I tried writting a parser but have been having trouble finding any
real documentation on the file format...
I think I can see the record marker as 0xFF and the FIELD seperators
as 0xFE with multivalue and sub markers 0xFD and 0xFC
I do not get a consistenet number of fields per record and other
oddities.
Any help or point me in the right direction... I clearly have no idea
how to do anything pick, but I am a pretty good engineer so a simple
file format defintion would solve a lot of my issues. I see more than
a few unprintalbe bytes so I think that they are trying to tell me
something but I cant seem to figure it out...
All the files start with a similar Hex sequence: 0a 01 ef ac
Can anyone help?
Ross Ferris
old version? If you game some examples of the file names you are
seeing, that may also lend a clue --> a would knowing the operating
system (well, OK, that is probably just nice to have)
There isn't anything necessarily "wrong" with there being different
number of fields/record --> the unprintable characters may indeed be
binary, and/or compiled code
I don't think that there IS any real documentation on the internal
foile formats that UV uses that is available to the public
Martin Phillips
> as 0xFE with multivalue and sub markers 0xFD and 0xFC
Beware! UV does not use the 0xFF in the same way as Pick. It is
actually between the record id and the data. There will be other 0xFF
characters as binary data in the record headers.
> All the files start with a similar Hex sequence: 0a 01 ef ac
This is not all that old (UV 9.3) and curretn versions can read these
files. If you have access to a UV system, the easiest answer is to use
UV to export the data.
The file format is documented as part of the UV internals course notes
but it is not just a matter of reading linearly from one end of the
file to the other.
Where is this data going? We have some tools that may do the job for
you even if you do not have a UV system to hand.
Martin Phillips, Ladybridge Systems Ltd
Douglas Tatelman
Is this a t-dump, a save, a unix dump?
You should just load them into a Universe system and then export in a
proper manner.
SFBoater
do know what the data in the files generally looks like (and which
data I am interested in extracting), but I dont know anything about
the environment that it runs in.
The ultimate destination for the data is a sql server schema. My plan
had been to parse the files into a raw sql server tables one record
per row, one field per column and then write a java/sql program to
massage the data into the custom schema. So an export of these files
as CSV files (one per file) would be ideal as it would import into sql
server easily.
So it sounds like I need to download the personal edition on UV.
(Done!)
And figure out how to import these into it and then figure out how to
export as csv file.
It also sounds like this may be easy. Any tips or instructions?
Ross Ferris
> It also sounds like this may be easy. Any tips or instructions?
Yeah - the PE edition has some "limits" in terms of database size,
which you may have exceeded
I'm also now concerhed about the nsource of this ZIP file - are you
working for the company that owns the data, or are you trying to
extract "secrets" from a dubious source
SFBoater
I am a new vendor for the owner of the data. We won the public RFP
and now I am converting the data from the legacy system into our own
web based system. I am the rightful owner of the data. Our customer
is not aware of any of the IT setup on their own site, they simply
zipped the folder and sent it to me. I am not able to ship this data
to anyone else for conversion, but I would appeciate instructions for
extracting to a CSV file. As well as help if I were to have a file
that was larger than allowable size limits.
Douglas Tatelman
whatever comes up.
Where are you located?
If this is from an active database, you need to be able to test this,
and then run the conversion in a timely manner when you cut over.
Simply asking here how to extract, when you don't even know how many
data files or anything is very simplistic.
Or else, you could tell your client that you just can't do it (for
free). Which wouldn't be true.
SFBoater
far...
Dowload personal copy on universe.
Download unidata
Create an accoung in the folder with the files...
Use the File tool in Unidata to look at a few files and run
diagnostics.
Everything seem ok...
And now to figure out how to export :) (feel to add tips to this
thread before I figure it out for myself :)
SFBoater
I have converted this form of the data in the past... it was in
advanced relevantions that time...
I know what the data looks like. I know where the data is going... I
know what files I want... it turns out the only problem is...
one of the files isn't showing up in the account... Neither in
UNIdata nor in the java connection with UniObjects...
a single file is too large for personal edition??? How would I
confirm this...
SFBoater
Here is the java program I wrote... if you know the the list of files
you want it works very well.... There might be a large file issue as
the only file I cant convert that I need with this tool is the largest
file... The file does not show up in my universe admin file tool
either... Weird... I hope this help someone or that it inspires
someone to help me...
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import asjava.uniclientlibs.UniDynArray;
import asjava.uniclientlibs.UniString;
import asjava.uniobjects.UniCommand;
import asjava.uniobjects.UniFile;
import asjava.uniobjects.UniSelectList;
import asjava.uniobjects.UniSelectListException;
import asjava.uniobjects.UniSession;
public class CT2RAW
{
boolean m_Debug = false;
Connection m_Con;
UniSession m_US;
public CT2RAW(File fileList, boolean debug, Connection con,
UniSession uSession)
throws Exception
{
m_Debug = debug;
m_Con = con;
m_US = uSession;
long time = System.currentTimeMillis();
HashMap ntext = new HashMap();
boolean testOutput = false;
if (!fileList.isFile())
{
throw new RuntimeException("File List not found:
"+fileList.getAbsolutePath());
}
BufferedReader br = new BufferedReader(new FileReader(fileList));
String file = br.readLine();
while (file!=null)
{
if (!file.startsWith("--"))
{
loadFile(file);
}
file = br.readLine();
}
time = System.currentTimeMillis() - time;
System.err.println("Total Time (minutes): "+((float)time)/1000/60);
}
private void loadFile(String name) throws Exception
{
System.err.println("\n\n\nBegin Working on table: "+name);
try
{
dropTable(name);
}
catch (Exception ignore)
{
//ignore exception due to nonexistent table
}
try
{
UniFile file = m_US.open(name);
UniSelectList records = m_US.selectList(1);
records.select(file);
UniCommand listRecords = m_US.command("LIST "+name);
UniString recordID = records.next();
int i=0;
PreparedStatement loader = null;
while (!records.isLastRecordRead())
{
if (!recordID.equals(""))
{
UniString rs = file.read(recordID);
UniDynArray record = new UniDynArray(rs);
if (i==0)
{
System.err.println (name+": "+recordID.toString()+".fields: "+
(record.dcount()+1));
createTable(name,record.dcount()+1);
loader = getPreparredStatement(name,record.dcount()+1);
System.err.println("Begin Loading... Update Every 2500 rows or
completeion");
}
/* Uncomment to Debug
if (name.equals("AUTO-TRAN000"))
{
System.err.println(i+": "+record.dcount());
m_Debug=true;
}
else
{
m_Debug = false;
}
*/
writeRow(loader,recordID,record,i++);
if (i%2500==0)
{
System.err.println(name+": Rows Inserted: "+i);
}
}
recordID = records.next();
}
loader.executeBatch();
System.err.println(name+": Rows Inserted: "+i);
file.close();
}
catch (UniSelectListException e)
{
e.printStackTrace();
}
}
private PreparedStatement getPreparredStatement(String tablename, int
fields) throws SQLException
{
String sql = "INSERT INTO ["+tablename+"] (";
for (int i=0; i<255; i++)
{
if (i!=0)
{
sql += ",";
}
sql += "[F"+i+"]";
}
sql += ") VALUES (";
for (int i=0; i<255; i++)
{
if (i!=0)
{
sql += ",";
}
sql += "?";
}
sql += ")";
PreparedStatement stmt = null;
return m_Con.prepareStatement(sql);
}
private void closeTable(Connection con, String tablename) throws
Exception
{
Statement stmt = null;
try
{
int num = 0;
stmt = con.createStatement();
//stmt.execute("update "+tablename+" set
f0=substring(f0,1+len(rtrim(ltrim(substring(f0,1,20))))-(select
min(len(rtrim(ltrim(substring(f0,1,20))))) from "+tablename+"),(select
min(len(rtrim(ltrim(substring(f0,1,20))))) from "+tablename+"))");
}
finally
{
close(stmt);
}
}
private void writeRow(PreparedStatement loader, UniString recordID,
UniDynArray record, int i) throws Exception
{
loadTable(loader,recordID, record);
if (i%80==0)
{
loader.executeBatch();
}
}
private void loadTable(PreparedStatement loader, UniString recordID,
UniDynArray record)
throws Exception
{
loader.setString(1,recordID.toString());
for (int i=1; i<(record.dcount()+1); i++)
{
UniDynArray field = record.extract(i);
if (m_Debug)
{
if (field.alpha())
{
System.err.println(field);
}
else
{
System.err.println("*binarydata*");
}
}
if (field.alpha())
{
loader.setString(i+1, field.toString());
}
else
{
loader.setString(i+1, "*binarydata*");
}
}
for (int i=(record.dcount()+1); i<255; i++)
{
loader.setString(i+1, "*emtpydata*");
}
loader.execute();
}
private void createTable(String tablename, int numCols)
throws Exception
{
String sql = "CREATE TABLE ["+tablename+"] (\n";
for (int i=0; i<255; i++)
{
if (i!=0)
{
sql += ",\n";
}
sql += "\t[F"+i+"] varchar(8000)";
sql += " NULL";
}
sql += "\n) ON [PRIMARY]";
System.err.println("Creating Table: "+tablename);
Statement stmt = null;
try
{
stmt = m_Con.createStatement();
stmt.execute(sql);
}
finally
{
close(stmt);
}
System.err.println("Done Creating Table");
}
private void dropTable(String tablename)
throws Exception
{
String sql = "DROP TABLE ["+tablename+"]";
System.err.println("Dropping Table: "+sql);
Statement stmt = null;
try
{
stmt = m_Con.createStatement();
stmt.execute(sql);
}
catch (Exception ingore) {}
finally
{
close(stmt);
}
System.err.println("Done Dropping Table");
}
private void close(Statement stmt)
throws Exception
{
if (stmt!=null)
{
stmt.close();
}
}
public static void main(String[] args)
{
if (args.length==8)
{
try
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection con =
DriverManager.getConnection(args[0],args[1],args[2]);
con.setAutoCommit(true);
UniSession uSession = new UniSession();
uSession.setHostName("localhost");
uSession.setUserName(args[3]);
uSession.setPassword(args[4]);
uSession.setAccountPath(args[5]);
uSession.connect();
CT2RAW ct2Raw = new CT2RAW(new
File(args[6]),Boolean.parseBoolean(args[7]) ,con, uSession);
con.close();
if (uSession.isActive())
{
uSession.disconnect();
}
}
catch (Exception e)
{
e.printStackTrace(System.err);
}
}
else
{
System.err.println("8 args: dburl, dbuser, dbpassword, uvuser,
uspassword, uv dir, filelist, debug");
}
}
}
Kevin Powick
> And now to figure out how to export :) (feel to add tips to this
> thread before I figure it out for myself :)
Helping people figure out how to move away from MV type systems is
usually not a very popular topic on this newsgroup.
I'm sure you may receive lots of reasons why your customer should not
have tried to move off of their existing MV system, but I guess it's
too late for that.
Good luck.
--
Kevin Powick
SFBoater
was to high for personal edition.
I was able to resize the modulo using
RESIZE <filename> * 10007 *
btw this is an outstanding resource and was a great help to me...
SFBoater
My apologies if I ruffeled any feathers... Just trying to do my
job... Thanks for the help!
SFBoater
The modulo was to high... I resized with
RESIZE <filename> * 10007 *
from the uv prompt. (put the bin directory in your path it makes it
easier)
x
I think you should listen to Douglas Tatelman's advice and hire a
consultant for the job.
Data conversion has to take place on the source system not on the
target one.
Currently you are not sure what system it is, Universe, Unidata or
maybe something else.
Each system has it's own file structure, similar but not quite the
same.
You complained that you didn't find a file.
You should be aware that on Multi Value system you can create an
alias, we call it Q-pointer, so the file you call ABC is in fact the
file XYZ found in some other account.
Extracting data from a file without knowing its structure is a guess
at best.
For example you don't know if the "record" you think you found is
really part of the data.
When you update a record in a file the end result may be moved to a
different location, for example in the overflow area, and the original
record may still be laying around with a delete flag set.
Let's say your customer tells you that the product description is
found in whatever file.
Wrong, you look in the file and you cannot find it.
Why, because it is not the product description but just a link to some
other file, we call it T-correlative, that has it.
Installing Universe or Unidata on a blank machine is pointless.
Maybe you are going to be able to read the files but that's all.
If your goal is to get the right data to the target machine then you
are shortchanged.
To get a better view about the task would be useful to search this
group for "conversion" or alike.
I am pretty sure you can easily find a Multi Value consultant in your
area willing to take the job.
Lucian
Kevin Powick
> Here is the java program I wrote...
Brutal. Not the code, I wouldn't actually know. It's just brutal
that's what it takes to get data into a RDMS.
> I hope this help someone or that it inspires
> someone to help me...
I'm afraid that if you want to work with an SQL-based RDBMS, you are
quite beyond help. Having worked extensively with both MV and RDBMSs
for many years, I say this from experience.
Again, best of luck.
--
Kevin Powick
frosty
>> Here is the java program I wrote...
Kevin Powick wrote:
>
> Brutal.
IAWTB!
--
frosty
Tony Gravagno
approach their U2 system from Java.
However, it doesn't handle data types like internal date format,
date=0 vs Null, multivalues, associated values, subvalues, file/table
names with characters invalid for a relational DB, and various other
considerations that might throw runtime exceptions, or simply yield
data that's tough to move from the F1/F2 fields that you're creating.
As with anything else, CSV is easy, relational is even easy, but it's
not going to come free. Data Conversion should have been a line item
in the RFP where professional consultation with a knowledgable MV
developer could have worked to make all of this happen.
T