CODEWITHSUNDEEP
c#csvssis
user2673722
user2673722

Reputation: 295

How to incorporate a text qualifier in a file name used as a variable?

I have a C# script which takes in two CSV files as input, combines the two files, performs numerous calculations on them, and writes the result in a new CSV file. These two input CSV file names are declared as variables and are used in the C# script by accessing those variable names.

The data in the input CSV files looks like this: enter image description here

Since the data has values in thousands and millions, line splits in the C# code are truncating the data incorrectly. For instance a value of 11,861 appears only as 11 and 681 goes in the next columns.

Is there any way in C#, by which I can specify a text qualifier (" in this case) for the two files ?

Here is the C# code snippet:

 string[,] filesToProcess = new string[2, 2] { {(String)Dts.Variables["csvFileNameUSD"].Value,"USD" }, {(String)Dts.Variables["csvFileNameCAD"].Value,"CAD" } };
string headline = "CustType,CategoryType,CategoryValue,DataType,Stock QTY,Stock Value,Floor QTY,Floor Value,Order Count,Currency";
string outPutFile = Dts.Variables["outputFile"].Value.ToString();
//Declare Output files to write to
FileStream sw = new System.IO.FileStream(outPutFile, System.IO.FileMode.Create);
StreamWriter w = new StreamWriter(sw);
w.WriteLine(headline);

//Loop Through the files one by one and write to output Files
for (int x = 0; x < filesToProcess.GetLength(1); x++)
{                
    if (System.IO.File.Exists(filesToProcess[x, 0]))
    {
        string categoryType = "";
        string custType = "";
        string dataType = "";
        string categoryValue = "";

        //Read the input file in memory and close after done
        StreamReader sr = new StreamReader(filesToProcess[x, 0]);
        string fileText = sr.ReadToEnd();
        string[] lines = fileText.Split(Convert.ToString(System.Environment.NewLine).ToCharArray());
        sr.Close();

where csvFileNameUSD and csvFileNameCAD are variables with values pointing to their locations.

Upvotes: 1

Views: 2419

Answers (2)

Dean
Dean

Reputation: 2273

Well, based on the questions you have answered, this ought to do what you want to do:

public void SomeMethodInYourCodeSnippet()
{
    string[] lines;
    using (StreamReader sr = new StreamReader(filesToProcess[x, 0]))
    {
        //Read the input file in memory and close after done
        string fileText = sr.ReadToEnd();
        lines = fileText.Split(Convert.ToString(System.Environment.NewLine).ToCharArray());
        sr.Close();  // redundant due to using, but just to be safe...
    }

    foreach (var line in lines)
    {
        string[] columnValues = GetColumnValuesFromLine(line);
        // Do whatever with your column values here...
    }
}
private string[] GetColumnValuesFromLine(string line)
{
    // Split on ","
    var values = line.Split(new string [] {"\",\""}, StringSplitOptions.None);
    if (values.Count() > 0)
    {
        // Trim leading double quote from first value
        var firstValue = values[0];
        if (firstValue.Length > 0)
            values[0] = firstValue.Substring(1);

        // Trim the trailing double quote from the last value
        var lastValue = values[values.Length - 1];
        if (lastValue.Length > 0)
            values[values.Length - 1] = lastValue.Substring(0, lastValue.Length - 1);
    }
    return values;
}

Give that a try and let me know how it works!

Upvotes: 2

rvphx
rvphx

Reputation: 2402

You posted a very similar looking question few days ago. Did that solution not help you?

If so, what issues are you facing on that. We can probably help you troubleshoot that as well.

Upvotes: 0

Related Questions