December 18, 2017 at 2:46 am
Dear all,
I have this txt file from where I will need to extract infromation (generate several excel files from it).
I am able already to connect to the file, open it and start reading.
When I try to read the first line (which is a *) I am not able, Instead it seems to start reading sometimes from the 3 line and others from the 2 lines.
Here is the file structure editted in note ++:
*
# 16 DIVIDENDS SAME DAY D_5011R.08 20171128
# 1 Calculation Date calc_date D 8 0
# 2 Reinvestment in index date reinvestment_in_index_date D 8 0
# 3 MSCI Security Code msci_security_code N 7 0
# 4 MSCI Timeseries Code msci_timeseries_code N 8 0
# 5 MSCI Dividend Code msci_dividend_code N 9 0
# 6 Xd-date xd_date D 8 0
# 7 Dividend description dividend_description S 15 0
# 8 Correction flag correction_flag N 1 0
# 9 Declared dividend amount unadjusted_dividend_amount N 15 5
# 10 Gross dividend amount adjusted_grs_dividend_amount N 15 5
# 11 Net dividend amount international adj_net_dividend_amount_int N 15 5
# 12 Net dividend amount domestic adj_net_dividend_amount_dom N 15 5
# 13 Dividend ISO currency symbol dividend_ISO_currency_symbol S 3 0
# 14 Dividend sub-unit dividend_sub_unit N 5 3
# 15 Dividend adjustment factor dividend_adjustment_factor N 10 5
# 16 DTR Number of Shares DTR_number_of_shares N 16 4
*
# 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
SSL>>>>>>>SSL>>>>>>>SSV>>>>>>SSV>>>>>>>SSV>>>>>>>>SSL>>>>>>>SSL>>>>>>>>>>>>>>SSVSSV>>>>>>>>>>>>>>SSV>>>>>>>>>>>>>>SSV>>>>>>>>>>>>>>SSV>>>>>>>>>>>>>>SSL>>SSV>>>>SSV>>>>>>>>>SSV>>>>>>>>>>>>>>>
| 20171128| 20171128| 1176901| 1953| 3655228| 20171128| Quarterly | 0| 0.06250| 0.06250| 0.04375| 0.04375| USD| 1.000| 1.00000| 336702111.0000
| 20171128| 20171128| 1683601| 4360| 3643888| 20171128| Quarterly | 0| 0.57000| 0.57000| 0.39900| 0.39900| USD| 1.000| 1.00000| 1477436517.0000
| 20171128| 20171128| 2099901| 61623| 3654138| 20171128| Final | 0| 1.30000| 1.30000| 1.30000| 1.30000| USD| 1.000| 1.00000| 41069286.0000
| 20171128| 20171128| 2355301| 64184| 3653645| 20171128| Final | 0| 0.45500| 0.45500| 0.34125| | CAD| 1.000| 1.00000| 613679213.0000
| 20171128| 20171128| 3062801| 72268| 3561489| 20171128| Final | 0| 3350.00000| 3350.00000| 2836.94750| 2669.44750| JPY| 1.000| 1.00000| 3055087.0000
| 20171128| 20171128| 7140301| 97562| 3554925| 20171128| Interim | 0| 4625.00000| 4625.00000| 3916.68125| 3685.43125| JPY| 1.000| 1.00000| 2049780.0000
#EOD
*
*
# 20 CURRENCY CONSTITUENTS SAME DAY D_5011R.11 20171128
# 1 Calculation Date calc_date D 8 0
# 2 As Of Date as_of_date D 8 0
# 3 MSCI Index Code msci_index_code N 6 0
# 4 Currency ISO Symbol currency_ISO_symbol S 3 0
# 5 Currency Name currency_name S 40 0
# 6 Currency weight in percentage currency_weight N 18 13
# 7 Previous Observation Date prev_obs_date D 8 0
# 8 Spot FX Rate Previous Obs Date spot_fx_prev_obs N 31 15
# 9 Previous Roll Date prev_roll_date D 8 0
# 10 Spot FX Rate Prev Roll Date spot_fx_prev_roll N 31 15
# 11 1 Month Fwd Rate Prev Roll Date forward_fx_30_prev_roll N 31 15
# 12 Spot FX Rate spot_fx_eod00d N 31 15
# 13 1 Month Forward Rate forward_fx_30_eod00d N 31 15
# 14 1 Week Forward Rate forward_fx_07_eod00d N 31 15
# 15 Interpolated Rate interpolated_rate N 16 12
# 16 One Month Interest Rate interest_rate_30 N 12 8
# 17 Nb of Days Until Next Rebal days_to_next_rebalancing N 3 0
# 18 Nb of Days Between Rebal days_between_rebal N 3 0
# 19 Implied Rate at Rebal Date implied_rate_rebal N 16 12
# 20 Notional Adjustment Factor notional_adjustment_factor N 17 13
*
# 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
SSL>>>>>>>SSL>>>>>>>SSV>>>>>SSL>>SSL>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>SSV>>>>>>>>>>>>>>>>>SSL>>>>>>>SSV>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>SSL>>>>>>>SSV>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>SSV>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>SSV>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>SSV>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>SSV>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>SSV>>>>>>>>>>>>>>>SSV>>>>>>>>>>>SSV>>SSV>>SSV>>>>>>>>>>>>>>>SSV>>>>>>>>>>>>>>>>
| 20171128| 20171128| 137232| AUD| AUSTRALIAN DOLLAR | 2.6395431173913| 20171030| 1.306492127592870| 20171031| 1.308392921908310| 1.311479316625450| 1.337176610689730| 1.340075304901920| 1.337840312478130| 1.337369856971| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| CAD| CANADIAN DOLLAR | 3.6666792306916| 20171030| 1.287376498319880| 20171031| 1.293238362760830| 1.295536221394570| 1.301920341394030| 1.303970924343230| 1.302382670746210| 1.302057046924| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| DKK| DANISH KRONE | 0.7195020577899| 20171030| 6.418827423642110| 20171031| 6.408105939004820| 6.409585990942770| 6.371824832351150| 6.373168136908070| 6.372165241875640| 6.371914385988| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| EUR| EURO | 12.5698289058567| 20171030| 0.862661217767729| 20171031| 0.861161657473020| 0.861475071491043| 0.856164860023253| 0.856429780885315| 0.856225237534540| 0.856182521414| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| GBP| POUND STERLING | 6.7659548919613| 20171030| 0.760210186789515| 20171031| 0.755457865788015| 0.756321730766842| 0.767992175081327| 0.768828060803594| 0.768185603287707| 0.768047900796| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| HKD| HONG KONG DOLLAR | 1.2180664407613| 20171030| 7.823912934450070| 20171031| 7.826845906902090| 7.839382379107040| 7.928012599065230| 7.941390561610160| 7.931208514185230| 7.928904463235| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| ILS| NEW ISRAELI SHEQEL | 0.0975778211965| 20171030| 3.536536436130200| 20171031| 3.531801765650080| 3.535497620088760| 3.556797398902660| 3.560164517721800| 3.557579515537170| 3.557021873491| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| JPY| YEN | 9.2546419835406| 20171030| 113.581423341191000| 20171031| 113.994783306581000| 114.076157159587000| 113.102011786222000| 113.178877390521000| 113.119834920764000| 113.107136159842| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| NOK| NORWEGIAN KRONE | 0.2676403906627| 20171030| 8.188173930487990| 20171031| 8.208166131621190| 8.218775916404050| 8.349674862832760| 8.359994502501870| 8.351921689012680| 8.350362838811| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| NZD| NEW ZEALAND DOLLAR | 0.0570574399393| 20171030| 1.460269807773220| 20171031| 1.464433651373180| 1.468321607310370| 1.468497828635060| 1.472114074171430| 1.469352065080140| 1.468738911671| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| SEK| SWEDISH KRONA | 1.1058938757265| 20171030| 8.385927077586640| 20171031| 8.398675762439810| 8.401393265881570| 8.478967689494000| 8.481173614045090| 8.479415920387900| 8.479114751131| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| SGD| SINGAPORE DOLLAR | 0.5000413981426| 20171030| 1.365314208335420| 20171031| 1.366773675762440| 1.369235563463460| 1.366998577524890| 1.369365700701950| 1.367509682141150| 1.367156385737| | 2| 30| 0.000000000000| 0.9992297618529
| 20171128| 20171128| 137232| USD| US DOLLAR | 61.1375724463395| 20171030| 1.003059330959430| 20171031| 1.003210272873190| 1.005242338796830| 1.016053647632590| 1.018055209133990| 1.016497758622440| 1.016187085066| | 2| 30| 0.000000000000| 0.9992297618529
#EOD
*
As for code to read it , I am using the following loop in a C# script task:
System.IO.StreamReader file = new System.IO.StreamReader(fileName);
while (i <= 2 && file.ReadLine() != null)
{
if (file.ReadLine().Replace(FileDelimiter, "|") == "|*|")
{
i = i + 1;
MessageBox.Show(file.ReadLine() + " - Passou pelo *");
}
else if (file.ReadLine() != "|*|" & z== 0 & i==0)
{
i = 4;
z = z + 1;
Filenameonly2 = (file.ReadLine().Replace(FileDelimiter, "_").Replace("#", ""));
MessageBox.Show(Filenameonly2);
}
}
file.Close();
Problem is that I was expec ting that the first stop of the cursor whould be :
if (file.ReadLine().Replace(FileDelimiter, "|") == "|*|")
{
i = i + 1;
MessageBox.Show(file.ReadLine() + " - Passou pelo *");
}
But it seems not to pass there. Can someone help?
December 20, 2017 at 1:33 pm
You've had no replies... probably because this isn't a SQL Server problem, per se... it's more of a problem with programming in C#. However, that said, some things that usually throw people for a loop when writing file I/O code in a procedural language are as follows:
1.) The incoming file's line ending characters are inconsistent or just not what was expected.
2.) The ReadLine function in either C# or VB expects a proper line ending, and will read data until it encounters one.
You should be able to take it from there...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 20, 2017 at 1:35 pm
And another thing.. If you use an IF statement where ReadLine is used, remember that it reads the line, and the line pointer will have moved, so you only get to test such a thing once.
You should instead always use ReadLine with a variable assignment instead, and then test the variable value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 4, 2018 at 8:41 am
Hello, Posting the solution. Hope this can help future questions related with this topic:
public void Main()
{
try
{
string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();
string DestinationFolderPath = Dts.Variables["User::DestinationFolderPath"].Value.ToString();
string FileName = Dts.Variables["User::FileName"].Value.ToString();
//string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
//string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
String[] myArrColumns = new string[1];
String[] myArrRecords= new string[1];
int i = 0; int z = 0; int ColArrayNum = 0; int SplitedWordsCounter = 0; int RecordArrayNum = 0; int CountColumns = 0;
String Filenameonly2 = "";
string SourceDirectory = SourceFolderPath;
//string[] fileEntries = Directory.GetFiles(SourceDirectory, "*");// + FileExtension);
//foreach (string fileName in fileEntries)
//{
System.IO.StreamReader file = new System.IO.StreamReader(FileName);
string[] lines = System.IO.File.ReadAllLines(FileName);
for (int l = 0; l < lines.Length; l++)
{
if (lines[l].Substring(0) == "*")
{
i++;
}
else if (lines[l].Substring(0) != "*" && i == 1 && z == 0)
{
Filenameonly2 = lines[l].Replace(" ", "()").Replace(")(", "").Replace("()", " ");
Filenameonly2 = Filenameonly2.Replace(" ", "_");
Filenameonly2 = Filenameonly2.Replace("#_", "");
z++;
}
else if (lines[l].Substring(0) != "*" && i == 1 && z == 1)
{
string ColumnName = lines[l].Substring(39, 31).Replace(" ", "");
if (myArrColumns.Length - 1 < ColArrayNum)
{
Array.Resize(ref myArrColumns, myArrColumns.Length + 1);
}
myArrColumns[ColArrayNum] = ColumnName.ToString();
ColArrayNum = ColArrayNum + 1;
}
else if (i == 2 && lines[l].StartsWith("|"))
{
lines[l] = lines[l].Substring(1);
string[] words = lines[l].Split('|');
RecordArrayNum = SplitedWordsCounter;
SplitedWordsCounter = SplitedWordsCounter + words.Length;
if (myArrRecords.Length < SplitedWordsCounter)
{
Array.Resize(ref myArrRecords, SplitedWordsCounter);
}
for (int e = 0; e < words.Length; e++)
{
myArrRecords[RecordArrayNum] = words[e];
RecordArrayNum ++;
}
}
else if (lines[l].Substring(0) == "#EOD")
{
File.Delete(DestinationFolderPath + "\\" + Filenameonly2 + ".csv");
string FilePath = DestinationFolderPath + "\\" + Filenameonly2 + ".csv";
int ArrayCount = myArrColumns.Length;
int ArrayCount2 = myArrRecords.Length;
StreamWriter sw = new StreamWriter(FilePath, true);
for (int j = 0; j < ArrayCount; j++)
{
string columns = myArrColumns[j];
sw.Write(columns);
if (j < ArrayCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
for (int ir = 0; ir < ArrayCount2; ir++)
{
string rows = myArrRecords[ir];
sw.Write(rows);
if (((ir < ArrayCount2) && ((ir + 1) % ArrayCount) != 0) || ir == 0)
{
sw.Write(",");
}
else
{
sw.Write(sw.NewLine);
}
}
sw.Close();
i = -1; z = 0; ColArrayNum = 0; SplitedWordsCounter = 0; RecordArrayNum = 0; CountColumns = 0;
Array.Resize(ref myArrColumns, 0); Array.Resize(ref myArrRecords, 0);
}
}
file.Close();
//}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception exception)
{
using (StreamWriter sw = File.CreateText(Dts.Variables["User::DestinationFolderPath"].Value.ToString()
+ "\\Error\\" + "ErrorLog_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply