public string CsvFix(string tablename, string csvtext)
{
List<string> result = new List<string>();
WvCsv csvhandler = new WvCsv(csvtext);
string coltype, colsstr;
List<string> values = new List<string>();
List<string> cols = new List<string>();
List<string> allcols = new List<string>();
int[] fieldstoskip = new int[skipfields.Count];
for (int i=0; i < skipfields.Count; i++)
fieldstoskip[i] = -1;
Dictionary<string,string> coltypes = new Dictionary<string,string>();
VxDiskSchema disk = new VxDiskSchema(exportdir);
VxSchema schema = disk.Get(null);
Console.WriteLine("Fixing data of table ["+tablename+"]");
string[] csvcolumns = (string[])csvhandler.GetLine()
.ToArray(Type.GetType("System.String"));
for (int i=0; i<csvcolumns.Length; i++)
csvcolumns[i] = csvcolumns[i].ToLower();
int ii = 0;
foreach (KeyValuePair<string,VxSchemaElement> p in schema)
{
if (!(p.Value is VxSchemaTable))
continue;
if (((VxSchemaTable)p.Value).name.ToLower() != tablename.ToLower())
continue;
foreach (VxSchemaTableElement te in ((VxSchemaTable)p.Value))
{
if (te.GetElemType() != "column")
continue;
if (csvcolumns.Contains(te.GetParam("name").ToLower()))
coltypes.Add(te.GetParam("name").ToLower(),
te.GetParam("type"));
allcols.Add(te.GetParam("name").ToLower());
if (csvcolumns.Contains(te.GetParam("name").ToLower()))
{
if (skipfields.Contains(te.GetParam("name").ToLower()))
fieldstoskip[skipfields.IndexOf(
te.GetParam("name").ToLower())] = ii;
else if (skipfields.Contains(
tablename.ToLower()+"."+
te.GetParam("name").ToLower()))
fieldstoskip[skipfields.IndexOf(
tablename.ToLower()+"."+
te.GetParam("name").ToLower())] = ii;
else
cols.Add(te.GetParam("name"));
}
ii++;
}
}
colsstr = "\"" + cols.join("\",\"") + "\"\n";
if (!csvhandler.hasMore())
return colsstr;
while (csvhandler.hasMore())
{
string[] asarray = (string[])csvhandler.GetLine()
.ToArray(Type.GetType("System.String"));
if (asarray.Length != csvcolumns.Length)
return "";
values.Clear();
for (int i=0;i<asarray.Length;i++)
{
if (Array.IndexOf(fieldstoskip,i)>=0)
continue;
if (replaces.ContainsKey(csvcolumns[i]))
asarray[i] = replaces[csvcolumns[i]];
if (replaces.ContainsKey(tablename.ToLower() + "." +
csvcolumns[i]))
asarray[i] = replaces[tablename.ToLower() + "." +
csvcolumns[i]].ToLower();
if (coltypes.ContainsKey(csvcolumns[i]) &&
(coltypes[csvcolumns[i]] != null))
coltype = coltypes[csvcolumns[i]];
else
coltype = "";
if (asarray[i] == null)
values.Add("");
else if ((coltype == "varchar") ||
(coltype == "datetime") ||
(coltype == "char") ||
(coltype == "nchar") ||
(coltype == "text"))
{
// Double-quote chars for SQL safety
string esc = asarray[i].Replace("\"", "\"\"");
//indication that single quotes are already doubled
if (esc.IndexOf("''") < 0)
esc = esc.Replace("'", "''");
if (WvCsv.RequiresQuotes(esc))
values.Add('"' + esc + '"');
else
values.Add(esc);
}
else if (coltype == "image")
{
string temp = asarray[i].Replace("\n","");
string tmp = "";
while (temp.Length > 0)
{
if (temp.Length > 75)
{
tmp += temp.Substring(0,76) + "\n";
temp = temp.Substring(76);
}
else
{
tmp += temp + "\n";
break;
}
}
values.Add("\""+tmp+"\"");
}
else
values.Add(asarray[i]);
}
result.Add(values.join(",") + "\n");
}
result.Sort(StringComparer.Ordinal);
return colsstr+result.join("");
}
public string Csv2Inserts(string tablename, string csvtext)
{
StringBuilder result = new StringBuilder();
WvCsv csvhandler = new WvCsv(csvtext);
ArrayList asarray, columns;
string sql;
string prefix = "";
bool has_ident = false;
var tab_names = new List<string>();
var coltypes = new Dictionary<string,string>();
VxSchema schema = new VxSchema();
string ident_seed, ident_incr, coltype;
tab_names.Add(tablename);
RetrieveTableSchema(schema, tab_names);
columns = csvhandler.GetLine(); //columns' names
string[] columns_array = (string[])columns.ToArray(
Type.GetType("System.String"));
foreach (KeyValuePair<string,VxSchemaElement> p in schema)
{
if (p.Value is VxSchemaTable)
{
foreach (VxSchemaTableElement te in ((VxSchemaTable)p.Value))
{
if (te.elemtype == "column")
{
if (columns_array.Contains(te.GetParam("name")))
{
coltypes.Add(te.GetParam("name"),te.GetParam("type"));
ident_seed = te.GetParam("identity_seed");
ident_incr = te.GetParam("identity_incr");
if (ident_seed.ne() && ident_incr.ne())
has_ident = true;
}
}
}
}
}
if (has_ident)
result.Append("SET IDENTITY_INSERT [" + tablename + "] ON;\n");
prefix = "INSERT INTO " + tablename + " ([" +
String.Join("],[",columns_array)+"]) VALUES (";
if (!csvhandler.hasMore())
return "";
while (csvhandler.hasMore())
{
sql = "";
asarray = csvhandler.GetLine();
if (asarray.Count < columns_array.Length)
return "";
for (int i=0;i<asarray.Count;i++)
{
sql += (i==0 ? prefix : ",");
coltype = "";
if (coltypes[columns_array[i]] != null)
coltype = coltypes[columns_array[i]];
if (asarray[i]!=null)
if ((coltype == "varchar") ||
(coltype == "datetime") ||
(coltype == "char") ||
(coltype == "image") )
if (coltype == "image")
sql += "0x"+bin2hex(System.Convert.FromBase64String(
asarray[i].ToString()
.Replace("\n","")));
else
sql += "'"
+ asarray[i].ToString().Replace("'", "''")
+ "'";
else
sql += asarray[i].ToString();
else
sql += "NULL";
}
result.Append(sql + ");\n");
}
if (has_ident)
result.Append("SET IDENTITY_INSERT [" + tablename + "] OFF;\n");
return result.ToString();
}