question

mionshion-3520 avatar image
0 Votes"
mionshion-3520 asked karenpayneoregon answered

CSV how can you remove all data that does not contain artist info or song name

Hello all i got a list of 172,500 lines of code from a csv that contains the artist name and song name,

but among all this, there is a lot of data i don't want i just want artist name and song name,

all the rest of the stuff i want gone but have no idea how to do this trying to use notepadd++ and used regex to try and do this but to no avail,

the file is here,
5Ub1mTxg




the issue i am having is i have no idea how to get just the name and artist there that is only a snippet as file is got a lot of data in any help would be much appriated
elfenliedtopfan5

dotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

vb2ae avatar image
0 Votes"
vb2ae answered

You could use the open source Nuget package CSVHelper to get the data from the file.


   class Program
    {
        static void Main(string[] args)
        {
             var config = new CsvConfiguration(CultureInfo.InvariantCulture)
             {
                   HasHeaderRecord = false,
             };
             List<SongData> records = null;
             using (var reader = new StreamReader("Csv File.csv"))
             {
                    using (var csv = new CsvReader(reader, config))
                    {
                           records = csv.GetRecords<SongData>().ToList();
                     }
              }
              foreach(var song in records)
              {
                     Console.WriteLine($"{song.Artist} - {song.SongName}");
              }
          }
    }


 public class SongData
 {

         [Index(1)]
         public string Artist { get; set; }

         [Index(12)]
         public string SongName { get; set; }
  
 }


Then you could write the data to a new file. There are examples on writing a new file in the CSVHelper link above.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DuaneArnold-0443 avatar image
0 Votes"
DuaneArnold-0443 answered

@mionshion-3520

You could use Linq to read the CVS file.

http://shortfastcode.blogspot.com/2011/12/using-linq-to-read-csv-file.html

You can change the code to project an anonymous type list using Linq.

https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/anonymous-types

Projecting Anonymous Types is section in below article

http://csharp-station.com/Tutorial/Linq/Lesson02

Then you can go into a foreach loop on the anonymous type list of objects and pick out what elements from each item/object's properties in the list you want to write to a new CVS file or whatever else you need to do.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

If you have SQL-Server Express edition you can perform a bulk insert, use a SQL SELECT WHERE to get at the data you want them empty the table.

Using a connection and command object example for the SQL

 BEGIN TRANSACTION
 BEGIN TRY
     BULK INSERT dbo.BulkDataTable
     FROM 'C:\SomePath\SomeFile.csv'
     WITH
     (
         FIELDTERMINATOR = ',',
         ROWTERMINATOR = '\n',
         ROWS_PER_BATCH = 10000, 
         TABLOCK
     )
        
     COMMIT TRANSACTION
    
 END TRY
 BEGIN CATCH
     ROLLBACK TRANSACTION
 END CATCH

Sample export code

 public static void ToCommaDelimited()
 {
     var selectStatement = "TODO";
        
     using var cn = new SqlConnection("TODO");
     using var cmd = new SqlCommand { Connection = cn, CommandText = selectStatement };
        
     cn.Open();
    
     var reader = cmd.ExecuteReader();
    
     var fileName = "C:\\SomePath\\test.csv";
     var streamWriter = new StreamWriter(fileName);
        
     var output = new object[reader.FieldCount];
    
     for (int index = 0; index < reader.FieldCount; index++)
     {
         output[index] = reader.GetName(index);
     }
    
     streamWriter.WriteLine(string.Join(",", output));
    
     while (reader.Read())
     {
         reader.GetValues(output);
         streamWriter.WriteLine(string.Join(",", output));
     }
 }


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.