vba – How can I combine multiple CSV files with different column names and column order in Excel?

I have over 350 CSV files I need to merge into one aggregate file. Using Power Query for Excel, I was able to easily merge all of the files in a few minutes. However, after inspection, not all of the files follow the same column order. For example, most files go by:

gender, city, address2, address1, state_province, social_handle, etc…

But some other files goes by:

gender, address2, city, state, address1, last_name, sopcial, etc…

Using Power Query, Excel, VBA, or something else – is there a way to effectively merge multiple multiple CSV files with different column names and column order? I tried searching online, but none of the solutions are related to what I was looking for (example).

performance – JMeter: how to asign a single distinct value from CSV Data Set Config to each thread in thread group?

I have to make a load test for a relatively large number of users so I cant really use User Parameters pre-processor to parametrize each thread with custom user data. I’ve read that I should use CSV Data Set Config instead. However I run into a problem with how JMeter interprets the input of this Config.

Example:

I have a thread group of 3 threads and Loop Count:10 with one HTTP request sampler with server www.example.com and path: ${user}.
The csv file (bullet is a single line in file) for CSV Data Set Config to extract the user parameter:

Expected output is that for thread 1-x the path of the request should be: x. So the output file should consist of 10 samples per thread namely:

  • for thread 1-1 : 10 requests to www.example.com1
  • for thread 1-2 : 10 requests to www.example.com2
  • for thread 1-3 : 10 requests to www.example.com3

but instead i get requests to each 1 – 5 and then to EOF. Does anyone know how to achieve the expected effect with CSV Data Set Config in jmeter 2.9?

python – “object of type ‘float’ has no len()” error in Spacy nlp() on CSV file made of strings

I am getting a “object of type ‘float’ has no len()” error while using Spacy’s nlp() on a CSV file encrypted in cp1252. This error seems quite straightforward, but all the text in this file was put there as strings. Most of the text clearly is fine because nlp() runs for quite a while before generating an error and because I am able to run my program on parts of my data.

Here is the code generating the error :

path=r"C:UsersarthuDropboxBig_data_general_informationPopulismNYTTestjCSVFile"+str(year)+".csv"
    df = pd.read_csv(path,encoding='cp1252')
    saved_column1=df('Content')
    saved_column2=df('Title')

    for content in saved_column1:
        Article_Counter=Article_Counter+1
        text=nlp(content)
        Status=False
        for word in text:
            Word_Counter=Word_Counter+1
            Title_Word_Counter=Title_Word_Counter+1
            l_word=str(word.lemma_)
            if l_word in L_Keywords:
                Keyword_Counter=Keyword_Counter+1
                Title_Keyword_Counter=Title_Keyword_Counter+1
                Status=True
                if l_word in count: 
                    count(l_word)=count(l_word)+1
                else:
                    count(l_word)=1
        if Status==True:
            Keyword_Article_Counter=Keyword_Article_Counter+1
            h.append('True')
        else:
            h.append('False')

Here is the code that downloaded the data :

           q =  queue.Queue()
            for elem in h:
                q.put(elem)
            def download():
                while not q.empty():
                    link=q.get() 
                    res = requests.get(link)
                    soup=bs4.BeautifulSoup(res.text,'lxml')
                    try: 
                        time.sleep(0.5)
                            
                        date = soup.select('.css-1u1psjv.epjyd6m3')
                        date = str(date(0).getText())
                           
                        title = soup.select('.css-1vkm6nb.ehdk2mb0')
                        title=str(title(0).getText())
                           
                        content0 = soup.select('.css-158dogj.evys1bk0')
                        content=''
                                        
                        for par in content0:
                            content = content+''+str(par.getText())
                        writer.writerow((date,title,content))
                    except:
                        print('Not an article')
            def main():
                with concurrent.futures.ThreadPoolExecutor() as executor:
                    (executor.submit(download) for i in range(0,10,1))    

            if __name__ == '__main__':
                main()
            print('Day',i,'month',j,'completed.')
file.close()
df = pd.read_csv(path,encoding='cp1252')
saved_column=df('Title')
x=len(saved_column)
for i in range(0,x,1):
    j=i+1
    while j<x:
        if saved_column(i)==saved_column(j):
            df=df.drop((i))
            break
        j=j+1
new_path=str(path)
df.to_csv(new_path,index=False,encoding='cp1252')

Here is the detailed error message:

runfile(‘C:/Users/arthu/OneDrive/Documents/Recherche/Word_Count.py’, wdir=’C:/Users/arthu/OneDrive/Documents/Recherche’)
Traceback (most recent call last):

File “C:UsersarthuOneDriveDocumentsRechercheWord_Count.py”, line 43, in
text=nlp(content)

File “C:ProgramDataAnaconda3libsite-packagesspacylanguage.py”, line 437, in call
if len(text) > self.max_length:

TypeError: object of type ‘float’ has no len()

Note that :

a) Ideally, I wouldn’t have to download the data all over again (it takes days of automated web crawling).

b) I can live with dropping some articles (lines) of my CSV file.

magento2 – programmatically importing images fails, but the csv file imports fine from admin in Magento 2

I’m trying to upload the following CSV file of images programmatically but it’s failing to import.
The weird thing is that it works from admin

sku,base_image,base_image_label,small_image,small_image_label,thumbnail_image,thumbnail_image_label,additional_images
20303050001,https://cdn.example.com/43835/e_43835_1.jpg,e_43835_1.jpg,https://cdn.example.com/43835/e_43835_1.jpg,e_43835_1.jpg,https://cdn.example.com/43835/e_43835_1.jpg,e_43835_1.jpg,https://cdn.example.com/43835/e_43835_2.jpg
20303050002,https://cdn.example.com/43836/e_43836_2.jpg,e_43836_2.jpg,https://cdn.example.com/43836/e_43836_2.jpg,e_43836_2.jpg,https://cdn.example.com/43836/e_43836_2.jpg,e_43836_2.jpg,https://cdn.example.com/43836/e_43836_2.jpg~https://cdn.example.com/43836/e_43836_1.jpg
20303050003,https://cdn.example.com/43837/e_43837_3.jpg,e_43837_3.jpg,https://cdn.example.com/43837/e_43837_3.jpg,e_43837_3.jpg,https://cdn.example.com/43837/e_43837_3.jpg,e_43837_3.jpg,https://cdn.example.com/43837/e_43837_2.jpg
20303050004,https://cdn.example.com/43838/e_43838_2.jpg,e_43838_2.jpg,https://cdn.example.com/43838/e_43838_2.jpg,e_43838_2.jpg,https://cdn.example.com/43838/e_43838_2.jpg,e_43838_2.jpg,https://cdn.example.com/43838/e_43838_1.jpg
20303050005,https://cdn.example.com/43839/e_43839_2.jpg,e_43839_2.jpg,https://cdn.example.com/43839/e_43839_2.jpg,e_43839_2.jpg,https://cdn.example.com/43839/e_43839_2.jpg,e_43839_2.jpg,https://cdn.example.com/43839/e_43839_2.jpg~https://cdn.example.com/43839/e_43839_1.jpg
20303050009,https://cdn.example.com/43843/e_43843_1.jpg,e_43843_1.jpg,https://cdn.example.com/43843/e_43843_1.jpg,e_43843_1.jpg,https://cdn.example.com/43843/e_43843_1.jpg,e_43843_1.jpg,https://cdn.example.com/43843/e_43843_1.jpg~https://cdn.example.com/43843/e_43843_2.jpg
20303050010,https://cdn.example.com/43844/e_43844_1.jpg,e_43844_1.jpg,https://cdn.example.com/43844/e_43844_1.jpg,e_43844_1.jpg,https://cdn.example.com/43844/e_43844_1.jpg,e_43844_1.jpg,https://cdn.example.com/43844/e_43844_1.jpg~https://cdn.example.com/43844/e_43844_2.jpg
20303050011,https://cdn.example.com/43845/e_43845_1.jpg,e_43845_1.jpg,https://cdn.example.com/43845/e_43845_1.jpg,e_43845_1.jpg,https://cdn.example.com/43845/e_43845_1.jpg,e_43845_1.jpg,https://cdn.example.com/43845/e_43845_2.jpg
20303050012,https://cdn.example.com/43846/e_43846_1.jpg,e_43846_1.jpg,https://cdn.example.com/43846/e_43846_1.jpg,e_43846_1.jpg,https://cdn.example.com/43846/e_43846_1.jpg,e_43846_1.jpg,https://cdn.example.com/43846/e_43846_1.jpg
20303050013,https://cdn.example.com/43847/e_43847_1.jpg,e_43847_1.jpg,https://cdn.example.com/43847/e_43847_1.jpg,e_43847_1.jpg,https://cdn.example.com/43847/e_43847_1.jpg,e_43847_1.jpg,https://cdn.example.com/43847/e_43847_1.jpg
20303050014,https://cdn.example.com/43848/e_43848_1.jpg,e_43848_1.jpg,https://cdn.example.com/43848/e_43848_1.jpg,e_43848_1.jpg,https://cdn.example.com/43848/e_43848_1.jpg,e_43848_1.jpg,https://cdn.example.com/43848/e_43848_1.jpg
20303050015,https://cdn.example.com/43849/e_43849_1.jpg,e_43849_1.jpg,https://cdn.example.com/43849/e_43849_1.jpg,e_43849_1.jpg,https://cdn.example.com/43849/e_43849_1.jpg,e_43849_1.jpg,https://cdn.example.com/43849/e_43849_1.jpg
20303050016,https://cdn.example.com/43850/e_43850_1.jpg,e_43850_1.jpg,https://cdn.example.com/43850/e_43850_1.jpg,e_43850_1.jpg,https://cdn.example.com/43850/e_43850_1.jpg,e_43850_1.jpg,https://cdn.example.com/43850/e_43850_1.jpg

So I’m trying to import using the following relevant code

$csv = Writer::createFromFileObject(new SplTempFileObject());
            $csv->insertOne($csvHeader);
            $csv->insertAll($csvData);
            $content = $csv->getContent();
            $importFolder = $this->directoryList->getPath(
                    DirectoryList::MEDIA
                ) . DIRECTORY_SEPARATOR . 'import';

            $timestamp = $runDate->getTimestamp();
            $importPath = $importFolder . DIRECTORY_SEPARATOR . $timestamp . 'import_images.csv';
            file_put_contents(
                $importPath,
                $content
            );


            $importFile = pathinfo($importPath);

            $import = $this->importFactory->create();
            $import->setData(
                (
                    'entity' => 'catalog_product',
                    'behavior' => Import::BEHAVIOR_APPEND,
                    Import::FIELD_NAME_VALIDATION_STRATEGY => 'validation-stop-on-errors',
                    Import::FIELD_FIELD_SEPARATOR => ",",
                    Import::FIELD_FIELD_MULTIPLE_VALUE_SEPARATOR => '~',
                    Import::FIELDS_ENCLOSURE => '',
                    Import::FIELD_EMPTY_ATTRIBUTE_VALUE_CONSTANT => Import::DEFAULT_EMPTY_ATTRIBUTE_VALUE_CONSTANT,
                    Import::FIELD_NAME_IMG_FILE_DIR => '',
                )
            );

            $import->setData('images_base_directory', $this->imagesDirProvider->getDirectory());

            $read_file = $this->readFactory->create($importFile('dirname'));
            $csvSource = $this->csvSourceFactory->create(
                (
                    'file' => $importFile('basename'),
                    'directory' => $read_file,
                )
            );

            $this->io->writeln("Validating data...");
            $validate = $import->validateSource($csvSource);
            if (!$validate) {
                $this->errIo->error('Unable to validate the CSV');
                $csvSourceCopy = clone $csvSource;
                foreach (
                    $import->getErrorAggregator()->getAllErrors() as $error
                ) {
                    $csvSourceCopy->rewind();
                    $csvSourceCopy->seek($error->getRowNumber());
                    $current = $csvSourceCopy->current();

                    $this->errIo->error(
                        sprintf(
                            "An error occured processing column %s at line %s with message %s",
                            $error->getColumnName(),
                            $error->getRowNumber(),
                            $error->getErrorMessage()
                        )
                    );

                    $this->errIo->error(
                        sprintf(
                            "Offending line %s",
                            json_encode($current)
                        )
                    );
                }

                rename($importPath, $importPath . '.err.csv');
                $this->errIo->error(
                    sprintf("Erroneous file saved at path %s", $importPath . '.err')
                );

                return false;
            }

            $this->io->writeln(sprintf("Importing data from file %s", $importPath));
            try{

                $result = $import->importSource();
                if ($result) {
                    $import->invalidateIndex();
                }
                unlink($importPath);

            }
            catch(Exception $exception)
            {
                $this->cliLogger->error(sprintf("An error occured while processing the file %s", $importPath));
                $this->cliLogger->error($exception->getMessage());

            }

The exception I receive when running $import->importSource() is

  SQLSTATE(23000): Integrity constraint violation: 1452 Cannot add or update a child row: a foreign
   key constraint fails (`db_example`.`catalog_product_entity_media_gallery_value_to_entity`, CONS
  TRAINT `CAT_PRD_ENTT_MDA_GLR_VAL_TO_ENTT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`)
  REFERENCES `catalog_p), query was: INSERT  INTO `catalog_product_entity_media_gallery_value_to_en
  tity` (`entity_id`,`value_id`) VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?,
   ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?,
  ?), (?, ?) ON DUPLICATE KEY UPDATE `value_id` = VALUES(`value_id`)


In Mysql.php line 91:

  SQLSTATE(23000): Integrity constraint violation: 1452 Cannot add or update a child row: a foreign
   key constraint fails (`db_example`.`catalog_product_entity_media_gallery_value_to_entity`, CONS
  TRAINT `CAT_PRD_ENTT_MDA_GLR_VAL_TO_ENTT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`)
  REFERENCES `catalog_p)

I’ve confirmed that my importModel has the same data as the importModel when running from the admin, and the file is the one I use in both cases as I take the generated file from my code then upload it via the admin page.

What could be the problem causing this error?

c# – How to synchronize data between a csv file and a database table?

At my work, a closed-source datalogger produces CSV-like data files:

"TOA5","MD","CR1000","49808","CR1000.Std.32.02","CPU:SampleProgram.CR1","10683","AveMetData"
"TIMESTAMP","RECORD","AveLatitude","AveLongitude","AveBatt_volt2","AvePTemp2","StdWSp1","WGst1","WSpdAve1","WSpdMean1","WDirAve1","StdWDir1","StdWSp2","WGst2","WSpdAve2","WSpdMean2","WDirAve2","StdWDir2"
"TS","RN","Degrees","Degrees","Volts","ºC","","m/sec","m/sec","m/sec","Degrees","","","m/sec","m/sec","m/sec","Degrees",""
"","","Smp","Smp","Smp","Smp","Smp","Smp","Smp","Smp","Smp","Smp","Smp","Smp","Smp","Smp","Smp","Smp"
"2020-06-08 16:20:00",768,"NAN","NAN",12.22,23.87,0,0,0,0,0,0,0,0,0,0,0,0
"2020-06-08 16:25:00",769,"NAN","NAN",12.22,23.84,0,0,0,0,0,0,0,0,0,0,0,0
"2020-06-08 16:30:00",770,29.61259,-90.7601,12.23,23.8,0,0,0,0,0,0,0,0,0,0,0,0
"2020-06-08 16:35:00",771,"NAN","NAN",12.24,23.84,0,0,0,0,0,0,0,0,0,0,0,0

There can be many such data files, each with different field names and units. The first line is header information that tells me some info about the datalogger. In the past, I’ve written code to parse each csv row and insert its values into a database table. But this is strictly “one” way synchronization: changes to the datatable values aren’t written out to file. Some problems that need to be addressed:

  1. How do I make changes bi-directional: changes to the datatable values are written out to the csv file and vice-versa.
  2. How do I make bi-directional changes efficient (e.g., one way to implement bi-directional changes is to completely rebuild the other source whenever one source changes. For example, if you modify one value of a record of the csv file, the old table is deleted and rebuilt from beginning to end with the new value. In the other direction, if you modify a value in a datatable, a stored procedure might execute which deletes the csv file and rebuilds it using the most recent values from the table? This is obviously very inefficient since it involves a lot of unnecessary copies/deletes.
  3. How do I make the synchronization automatic: do I periodically compare the csv file/data table for differences or should a service continually monitor the file/table for changes?

I’m currently using C# (.NET Framework) but solutions in C++ will work too.

java – How do I count number of lines in an imported to array CSV file?

How do I count the number of lines in an imported to an array CSV file?
I have 5 lines in the csv? I’ve tried several methods, such as in the bufferedreader, but I couldn’t get it to work. I’m still new to Java, so I’m 100% accross concepts.

package carrentalsystem;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.Scanner;

public class MenuDisplay {

public static void displayCarList() throws FileNotFoundException,
IOException {

String path = "carlist.csv";
long lines = 0;

String line = "";

System.out.println("*************************************************" + "**************");
System.out.println("       Welcome to the Carrington Car Rental");
System.out.println("*************************************************" + "**************");
System.out.println("Cars available for booking");
System.out.println("_________________________________________________" + "_____________");
try {
  BufferedReader br = new BufferedReader(new FileReader(path));

  while ((line = br.readLine()) != null) {
    String() menu = line.split(",");
    System.out.println(menu(0) + "t" + menu(1) + "t" + menu(2) + "t" + menu(3) + "t" + menu(4) + "t" + menu(5));

  }
} catch(FileNotFoundException e) {
  e.printStackTrace();
} catch(IOException e) {
  e.printStackTrace();
}

System.out.println("_________________________________________________" + "_____________");
System.out.println("Total of" + "cars available." + "n");

System.out.println("* Note for premium cars, there's addition 5% insurance" + " access applied to the car rate");
System.out.println("*************************************************");
System.out.println("n");
    getSelection();

  }

  public static void getSelection() {
    Scanner scan = new Scanner(System. in );
    System.out.println("Select from the following options." + "n");
    System.out.println("1. To make a booking");
    System.out.println("2. To exit the System");
    System.out.println("n");
    System.out.println("Enter your selection: ");
    int selection = scan.nextInt();
    CarAndBookingDates b;
    switch (selection) {
    case 1:
      b = new CarAndBookingDates();
      b.carSelection();
      b.promptForYear();
      b.promptForMonth();
      b.promptForDay();
      b.getCarBookingDateFull();
      break;
    case 2:
      System.out.println("Good Bye!");
      System.exit(0);
      break;
    default:
      System.out.println("INVALID");
      break;

    }

  }

}

java – Migrate customer and transaction data from CSV to MongoDB

I wrote a Java Spring Boot application with MongoDB that on every application start loads customer, account type and transaction details from CSV files to MongoDB. It has one end point that returns transactions for a given customerId and accountType (for example customer id 1,3,5 etc. or account type “ALL”). The main target is to return demanded data (and sorted ascending by transaction amount) as fast as possible (read time matters most). I also wrote 3 tests to test this end point.

My service:

package com.colliers.mongodb.service;

import com.colliers.mongodb.exception.TransactionNotFoundException;
import com.colliers.mongodb.model.DTO.TransactionDTO;
import com.colliers.mongodb.model.Transaction;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Service
public class TransactionService {

    private MongoTemplate mongoTemplate;

    @Autowired
    public TransactionService(MongoTemplate mongoTemplate) {
       this.mongoTemplate = mongoTemplate;
    }

    public List<TransactionDTO> getTransactions(String accountType, String customerId) throws TransactionNotFoundException {
        final List<String> accounts = Arrays.asList(accountType.split(","));
        final List<String> customers = Arrays.asList(customerId.split(","));

        Query query = new Query();
        query.with(Sort.by(Sort.Direction.ASC, "transactionAmount"));

        if(accountType.equals("") || accountType.equals("ALL"));
        else {
            query.addCriteria(Criteria.where("accountType.accountType").in(accounts));
        }

        if(customerId.equals("") || customerId.equals("ALL"));
        else {
            query.addCriteria(Criteria.where("customer.id").in(customers));
        }

        List<Transaction> transactions = this.mongoTemplate.find(query, Transaction.class, "transactions");

        if(transactions.isEmpty()) {
            throw new TransactionNotFoundException("No Transactions found.");
        }

        List<TransactionDTO> result = new ArrayList<>();

        for(var t : transactions) {
            TransactionDTO transactionDTO = TransactionDTO.builder()
                    .transactionDate(t.getTransactionDate())
                    .transactionId(t.getId())
                    .transactionAmount(t.getTransactionAmount())
                    .accountTypeName(t.getAccountType().getName())
                    .firstName(t.getCustomer().getFirstName())
                    .lastName(t.getCustomer().getLastName())
                    .build();

            result.add(transactionDTO);
        }

        return result;
    }
}

My Controller:

package com.colliers.mongodb.controller;

import com.colliers.mongodb.model.DTO.TransactionDTO;
import com.colliers.mongodb.service.TransactionService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

@RestController
class Controller {

    private TransactionService service;

    @Autowired
    Controller(TransactionService service) {
        this.service = service;
    }

    @GetMapping("/transactions")
    ResponseEntity<List<TransactionDTO>> getTransactionsByAccountTypeAndCustomerId(
        @RequestParam(value = "account_type") String accountType, @RequestParam(value = "customer_id") String customerId) {

        String regex = "^(^$)|(ALL)|(((1-9)(0-9)*)((,(1-9)(0-9)*)*))$";

        if(!accountType.matches(regex) || !customerId.matches(regex)) {
            return new ResponseEntity<>(HttpStatus.BAD_REQUEST);
        }

        return new ResponseEntity<>(this.service.getTransactions(accountType, customerId), HttpStatus.OK);
    }
}

My AppInit – database seeder:

package com.colliers.mongodb.configuration;

import com.colliers.mongodb.model.AccountType;
import com.colliers.mongodb.model.Customer;
import com.colliers.mongodb.model.Transaction;
import com.colliers.mongodb.repository.AccountTypeRepository;
import com.colliers.mongodb.repository.CustomerRepository;
import com.colliers.mongodb.repository.TransactionRepository;
import com.opencsv.CSVReader;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.CsvToBeanBuilder;
import com.opencsv.bean.HeaderColumnNameTranslateMappingStrategy;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.stereotype.Component;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Component
class AppInit implements CommandLineRunner {

    private CustomerRepository customerRepository;
    private TransactionRepository transactionRepository;
    private AccountTypeRepository accountTypeRepository;
    private MongoTemplate mongoTemplate;

    @Autowired
    public AppInit(CustomerRepository customerRepository, TransactionRepository transactionRepository,
                   AccountTypeRepository accountTypeRepository, MongoTemplate mongoTemplate) {

        this.customerRepository = customerRepository;
        this.transactionRepository = transactionRepository;
        this.accountTypeRepository = accountTypeRepository;
        this.mongoTemplate = mongoTemplate;
    }

    @Override
    public void run(String... args) throws Exception {
        mongoTemplate.dropCollection("customers");
        mongoTemplate.dropCollection("transactions");
        mongoTemplate.dropCollection("accountypes");
        mongoTemplate.createCollection("customers");
        mongoTemplate.createCollection("transactions");
        mongoTemplate.createCollection("accountypes");

        File customersCSV = new File("src/main/resources/static/customers.csv");
        File transactionsCSV = new File("src/main/resources/static/transactions.csv");
        File accountypesCSV = new File("src/main/resources/static/accountypes.csv");

        Map<String, Customer> customers = parseCustomers(customersCSV);
        customerRepository.saveAll(customers.values());

        Map<String, AccountType> accountTypes = parseAccountTypes(accountypesCSV);
        accountTypeRepository.saveAll(accountTypes.values());

        List<Transaction> transactions = parseTransactions(transactionsCSV);
        for(var t : transactions) {
            t.setAccountType(accountTypes.get(t.getAccountTypeNumber()));
            t.setCustomer(customers.get(t.getCustomerId()));
        }

        transactionRepository.saveAll(transactions);
    }

    private List<Transaction> parseTransactions(File file) throws Exception {
        HeaderColumnNameTranslateMappingStrategy<Transaction> strategy = new HeaderColumnNameTranslateMappingStrategy<>();
        strategy.setType(Transaction.class);

        Map<String, String> columnMapping = new HashMap<>();
        columnMapping.put("transaction_id", "id");
        columnMapping.put("transaction_amount", "transactionAmount");
        columnMapping.put("account_type", "accountTypeNumber");
        columnMapping.put("customer_id", "customerId");
        columnMapping.put("transaction_date", "transactionDate");

        strategy.setColumnMapping(columnMapping);

        CSVReader reader = new CSVReader(new FileReader(file.getPath()));
        CsvToBean<Transaction> csvToBean = new CsvToBeanBuilder<Transaction>(reader)
                .withMappingStrategy(strategy)
                .build();

        return csvToBean.parse();
    }

    private Map<String, Customer> parseCustomers(File file) throws IOException {
        HeaderColumnNameTranslateMappingStrategy<Customer> strategy = new HeaderColumnNameTranslateMappingStrategy<>();
        strategy.setType(Customer.class);

        Map<String, String> columnMapping = new HashMap<>();
        columnMapping.put("id", "id");
        columnMapping.put("first_name", "firstName");
        columnMapping.put("last_name", "lastName");
        columnMapping.put("last_login_balance", "lastLoginBalance");

        strategy.setColumnMapping(columnMapping);

        CSVReader reader = new CSVReader(new FileReader(file.getPath()));
        CsvToBean<Customer> csvToBean = new CsvToBeanBuilder<Customer>(reader)
                .withMappingStrategy(strategy)
                .build();

        return csvToBean.parse().stream().collect(Collectors.toMap(Customer::getId, c -> c));
    }

    private Map<String, AccountType> parseAccountTypes(File file) throws IOException {
        HeaderColumnNameTranslateMappingStrategy<AccountType> strategy = new HeaderColumnNameTranslateMappingStrategy<>();
        strategy.setType(AccountType.class);


        Map<String, String> columnMapping = new HashMap<>();
        columnMapping.put("account_type", "accountType");
        columnMapping.put("name", "name");

        strategy.setColumnMapping(columnMapping);

        CSVReader reader = new CSVReader(new FileReader(file.getPath()));
        CsvToBean<AccountType> csvToBean = new CsvToBeanBuilder<AccountType>(reader)
                .withMappingStrategy(strategy)
                .build();

        return csvToBean.parse().stream().collect(Collectors.toMap(AccountType::getAccountType, a -> a));
    }
}

My ControllerTest:

package com.colliers.mongodb.controller;

import com.colliers.mongodb.model.AccountType;
import com.colliers.mongodb.model.Customer;
import com.colliers.mongodb.model.DTO.TransactionDTO;
import com.colliers.mongodb.model.Transaction;
import com.colliers.mongodb.service.TransactionService;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.mockito.ArgumentMatchers;
import org.mockito.Mock;
import org.mockito.junit.jupiter.MockitoExtension;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.http.ResponseEntity;
import java.util.Arrays;
import java.util.List;
import static org.assertj.core.api.Assertions.assertThat;
import static org.mockito.Mockito.when;

@ExtendWith(MockitoExtension.class)
class ControllerTest {

    @Mock
    private MongoTemplate mongoTemplate;

    private Controller controller;

    @BeforeEach
    void setup() {
        TransactionService service = new TransactionService(mongoTemplate);
        controller = new Controller(service);
    }

    @Test
    void test() {}

    @Test
    void getTransactionsByAccountTypeAndCustomerIdWithWIthOneAccountTypeAndOneCustomerIdTest() {
        Transaction t1 = Transaction.builder()
                .id(700)
                .transactionAmount(600.11)
                .accountType(new AccountType("1", "1", "checking account"))
                .customer(new Customer("1", "Elliot", "Alderson", 199))
                .transactionDate("2017-03-06 13:23:11")
                .build();

        when(this.mongoTemplate.find(ArgumentMatchers.any(Query.class), ArgumentMatchers.eq(Transaction.class),
                                            ArgumentMatchers.anyString())).thenReturn(Arrays.asList(t1));

        ResponseEntity<List<TransactionDTO>> result = controller.getTransactionsByAccountTypeAndCustomerId("1", "1");
        assertThat(result.getBody().get(0).getAccountTypeName()).isEqualTo("checking account");
        assertThat(result.getBody().get(0).getFirstName()).isEqualTo("Elliot");
    }

    @Test
    void getTransactionsByAccountTypeAndCustomerIdWithOneCustomerIdAndEmptyAccountTypeTest() {
        Transaction t1 = Transaction.builder()
                .id(700)
                .transactionAmount(600.11)
                .accountType(new AccountType("1", "1", "checking account"))
                .customer(new Customer("1", "Elliot", "Alderson", 199))
                .transactionDate("2017-03-06 13:23:11")
                .build();

        Transaction t2 = Transaction.builder()
                .id(723)
                .transactionAmount(524.32)
                .accountType(new AccountType("2", "2", "saving account"))
                .customer(new Customer("1", "Elliot", "Alderson", 199))
                .transactionDate("2013-08-04 23:57:38")
                .build();

        when(mongoTemplate.find(ArgumentMatchers.any(Query.class), ArgumentMatchers.eq(Transaction.class),
                ArgumentMatchers.anyString())).thenReturn(Arrays.asList(t1, t2));

        ResponseEntity<List<TransactionDTO>> result = controller.getTransactionsByAccountTypeAndCustomerId("", "1");
        assertThat(result.getBody().get(0).getAccountTypeName()).isEqualTo("checking account");
        assertThat(result.getBody().get(0).getFirstName()).isEqualTo("Elliot");

        assertThat(result.getBody().get(1).getAccountTypeName()).isEqualTo("saving account");
        assertThat(result.getBody().get(1).getFirstName()).isEqualTo("Elliot");
    }

    @Test
    void getTransactionsByAccountTypeAndCustomerIdWithEmptyAccountTypeAndALLCustomerIdTest() {
        Transaction t1 = Transaction.builder()
                .id(700)
                .transactionAmount(600.11)
                .accountType(new AccountType("1", "1", "checking account"))
                .customer(new Customer("1", "Elliot", "Alderson", 199))
                .transactionDate("2017-03-06 13:23:11")
                .build();

        Transaction t2 = Transaction.builder()
                .id(723)
                .transactionAmount(524.32)
                .accountType(new AccountType("2", "2", "saving account"))
                .customer(new Customer("1", "Elliot", "Alderson", 199))
                .transactionDate("2013-08-04 23:57:38")
                .build();

        Transaction t3 = Transaction.builder()
                .id(200)
                .transactionAmount(500)
                .accountType(new AccountType("1", "1", "checking account"))
                .customer(new Customer("2", "Tyrell", "Wellick", 10000))
                .transactionDate("2020-10-07 23:59:59")
                .build();

        Transaction t4 = Transaction.builder()
                .id(540)
                .transactionAmount(1000)
                .accountType(new AccountType("2", "2", "saving account"))
                .customer(new Customer("2", "Tyrell", "Wellick", 10000))
                .transactionDate("2018-02-01 12:05:18")
                .build();

        when(mongoTemplate.find(ArgumentMatchers.any(Query.class), ArgumentMatchers.eq(Transaction.class),
                ArgumentMatchers.anyString())).thenReturn(Arrays.asList(t1, t2, t3, t4));

        ResponseEntity<List<TransactionDTO>> result = controller.getTransactionsByAccountTypeAndCustomerId("", "ALL");
        assertThat(result.getBody().get(0).getAccountTypeName()).isEqualTo("checking account");
        assertThat(result.getBody().get(0).getFirstName()).isEqualTo("Elliot");

        assertThat(result.getBody().get(1).getAccountTypeName()).isEqualTo("saving account");
        assertThat(result.getBody().get(1).getFirstName()).isEqualTo("Elliot");

        assertThat(result.getBody().get(2).getAccountTypeName()).isEqualTo("checking account");
        assertThat(result.getBody().get(2).getFirstName()).isEqualTo("Tyrell");

        assertThat(result.getBody().get(3).getAccountTypeName()).isEqualTo("saving account");
        assertThat(result.getBody().get(3).getFirstName()).isEqualTo("Tyrell");
    }
}

This application is all that I am currently capable of (I want also to note, that I’m not a professional software dev – still learning and want to become one in the future). In order to keep improving I need feedback concerning topics like “clean code”, don’t do that – do that, use different library etc. I’m not interested in discussing app security (topic on different time). Below link to GitHub repo.

https://github.com/dstarostka/mongodb

node.js – Importando CSV com NodeJS Problemas com primeira coluna do CSV

Boa noite pessoal, alguém já teve o problema na importação de um CSV onde a primeira coluna do arquivo vem como undefined?

// import_customers_service

import_customers_service('./data_files/customers_service.csv');

function import_customers_service(filename){

    let stream = fs.createReadStream(filename);
    let parser = csv.parse({
        delimiter: ',',
        columns: true
    });

    let transform = csv.transform(function(row) {
        
        let resultObj = {
            id: row('id'),
            name: row('name')
        }

        console.log(resultObj);

    });

    stream.pipe(parser).pipe(transform)

}

Aquivo CSV

id,name
1,Yes
2,No
3,No phone service

Resultado:

{ id: undefined, name: 'Yes' }
{ id: undefined, name: 'No' }
{ id: undefined, name: 'No phone service' }

Alguém já viu algo sobre esse erro?

Abraço

powershell – Need help to export directories input by the user to a .csv file in proper format and import these dirs into another script for periodic robocopy

#Script 1 to take user input of directory
 
#Directories 
$Dir1=Read-Host -Prompt "Enter first location" |Out-File -FilePath .directories.csv -Append
$Dir2=Read-Host -Prompt "Enter 2nd location (or leave blank)" |Out-file -FilePath .directories.csv -Append
$Dir3=Read-Host -Prompt "3rd" | Out-file -FilePath .directories.csv -Append
$Dir4=Read-Host -Prompt "4th" | Out-file -FilePath .directories.csv -Append
$Dir5=Read-Host -Prompt "5th" | Out-file -FilePath .directories.csv -Append
$Destination="D:TestDestforPS" | Out-file -FilePath .directories.csv -Append
 
#Script 2 which will be scheduled to run periodically
 
$Dir=Import-csv 'D:PowerShell Scripts and ToolsPublicdirectories.csv' 
ForEach ($_ in $Dir) {robocopy $_ $Destination /E /XX /COPYALL |Out-File $Destinationlogs.txt -Append}

Script2 is unable to Robocopy because the dirs get exported from 1 and imported into script 2 with headers so an error pops-up something like this “D:@{SelectedPath=D:
TestPS}
The filename, directory name, or volume label syntax is incorrect.”

I get that the job can be done without import/export of csv but I want to make it user-friendly now so that one can easily edit the dirs if need be

transactions – Why does Bitcoincharts have inactive CSV data dumps in the non-inactive dir?

I discovered this goldmine of Bitcoin transaction data today, and have been coding all day to download them all and insert into my local database table: https://api.bitcoincharts.com/v1/csv/

However, something confuses me about this. As you can see, there is a sub-directory literally called “inactive_exchanges”, leading me to assume that all the dead exchanges’ data is in that dir. And it does contain a bunch of old files. So far: perfectly logical.

However, I’ve discovered that a lot of the data dump files in the main dir actually are from dead exchanges. For example, the abucoinsUSD one, if downloaded and looked at, has records only between:

2017-09-21 16:03:06
2018-05-29 16:26:15

Sure, they may not have data before 2017 because they may have started that year, but 2018 is the last year where they have data, and the current year is 2020. That means it’s been dead or stale for two years now. Yet it’s not in the “inactive exchanges” dir. How can this be explained?

I was extremely happy when I finally found such a good data source, but of course there’s always some catch. In this case, it seems to be that they have a bunch of dead exchanges still in the “live” dir, which makes me worried about the quality of the data in general.

At least it seems so far like the big files, for the known exchanges, have full data. But still, I wish I could get this straightened out.

Please note: I’ve already e-mailed them about other things and they don’t respond, like 99% of all companies/people I e-mail these days. So I can’t “just ask them”.