In one of my recent project Client gave me a text file having 15000 records and tell me that data is not inserting in to sybase db, find and resole it with cause? HE also quoted me that he has spent so much time to diagnose what is going on in those record ?
Well nearly I found the issue and fix it for always even for millions record, the issue came around due to one column having long string which has some spaces (but that space has some junk data in that) by which sybase found that space as gabage and dont let system to isnert row into database.actually in that text file I found insertion commands for table in those 15000 records with data, which will be fired on the sybase server. I tried same file on SQL it works, that data given to me by client was generated by one portal having diferent language, which is root cause inserting garbage vlues in between space in that data.
First to remove the garbasge vlues I split all 15000 record by new ilne, then on each row I replace empty space with (|) pipedelimiter and against make it back to space, and it solved🙂
another requiredment of client was that to put a NULL to only a one column in each row of a file, which values is ‘ ‘ (empty) in the reocord because it has datetime datatype in table on sybase and If sybase server found null (‘ ‘) so thet It put 1900 in that column or default date,
Each row has structured like that :
Insert into Table (a,b,date_col,date_col,date_dt,d) vlaues (‘firstcolumnvlaue,”,”,’this is the comment column, in which I fond some commas, like 1,000,33’, ”,”)
Now second last ” (empty) string needed to be NULL and its lie (”) empty charaters ‘ , so for this I need to split entire row with commas, but it didnt work, because C# compilar found commas in comment columns as well, for captrue this I used the regular epxression which check the commas (,) which is out side the string, like that :
string _check_Null_Date = Regex.Split(SingBrach, “,(?=(?:[^’]*'[^’]*’)*[^’]*$)”);
This gave me a string array and I fiexed as it as original requirement. This regular expression check the commas which are outside in the string.